Create LINQ to select from multiple tables

Question

This query works fine:

var pageObject = (from op in db.ObjectPermissions join pg in db.Pages on op.ObjectPermissionName equals page.PageName where pg.PageID == page.PageID select op) .SingleOrDefault(); 

I get a new type with "op" fields. Now I also want to get the "pg" fields, but

 select op, pg).SingleOrDefault(); 

does not work.

How can I select all of both tables so that they appear in my new pageObject type?

Answer 1

You can use anonymous types for this, i.e.:

 var pageObject = (from op in db.ObjectPermissions join pg in db.Pages on op.ObjectPermissionName equals page.PageName where pg.PageID == page.PageID select new { pg, op }).SingleOrDefault(); 

This will make pageObject an IEnumerable of anonymous type, so AFAIK you will not be able to pass it to other methods, however, if you just get the data to play in the method that you are using now perfectly. You can also name properties in your anonymous type, that is: -

 var pageObject = (from op in db.ObjectPermissions join pg in db.Pages on op.ObjectPermissionName equals page.PageName where pg.PageID == page.PageID select new { PermissionName = pg, ObjectPermission = op }).SingleOrDefault(); 

This will let you say: -

 if (pageObject.PermissionName.FooBar == "golden goose") Application.Exit(); 

For example: -)

Answer 2

If you do not want to use anonymous b / c types, say that you are passing the object to another method, you can use the LoadWith load parameter to load the associated data. This requires your tables to be linked either through foreign keys or in your Linq-to-SQL dbml model.

 db.DeferredLoadingEnabled = false; DataLoadOptions dlo = new DataLoadOptions(); dlo.LoadWith<ObjectPermissions>(op => op.Pages) db.LoadOptions = dlo; var pageObject = from op in db.ObjectPermissions select op; // no join needed 

Then you can call

 pageObject.Pages.PageID 

Depending on how your data looks, you probably want to do it the other way around,

 DataLoadOptions dlo = new DataLoadOptions(); dlo.LoadWith<Pages>(p => p.ObjectPermissions) db.LoadOptions = dlo; var pageObject = from p in db.Pages select p; // no join needed var objectPermissionName = pageObject.ObjectPermissions.ObjectPermissionName; 

Answer 3

You must create a new anonymous type:

  select new { op, pg } 

Refer to the official manual .

Answer 4

If the anonymous type is causing you problems, you can create a simple data class:

 public class PermissionsAndPages { public ObjectPermissions Permissions {get;set} public Pages Pages {get;set} } 

and then in your request:

 select new PermissionsAndPages { Permissions = op, Page = pg }; 

Then you can pass this:

 return queryResult.SingleOrDefault(); // as PermissionsAndPages 

Answer 5

change

 select op) 

to

 select new { op, pg })