linq to sql using foreign keys returning iqueryable(of myEntity]
- by Gern Blandston
I'm trying to use Linq to SQL to return an IQueryable(of Project) when using foreign key relationships. Using the below schema, I want to be able to pass in a UserId and get all the projects created for the company the user is associated with.
DB tables:
Projects
Projid
ProjCreator FK (UserId from UserInfo table)
Companyid FK (CompanyID from Companies table)
UserInfo
UserID PK
Companyid FK
Companies
CompanyId PK
Description
I can get the iqueryable(of project) when simply getting the ProjectCreator with this:
Return (From p In db.Projects _
Where p.ProjectCreator = Me.UserId)
But I'm having trouble getting the syntax to get a iqueryable(of projects) when using foreign keys. Below gives me an IQueryable(of anonymous) but I can't seem to convince it to give me an IQueryable(of project) even if I try to cast it:
Dim retval = (From p In db.Projects _
Join c In db.Companies On p.CompanyId Equals c.CompanyId _
Join u In db.UserInfos On u.CompanyId Equals c.CompanyId _
Where u.Login = UserId)
