Entityframework Join using join method and lambdas
It seems there are different ways to do joins using linq. One is more straightforward and involves just joining tables like this:
var found = from c in s.categories
join cm in s.categorymaps on c.CategoryId equals cm.ChildCategoryId
select c;
There is another way using lambdas and I'm having a heck of a time trying to figure out how to join using this syntax. Can anyone provide links to detailed explanations and lots of examples? Or just simply demonstrate using examples how to use this rather confusing syntax?
var x = _session.All<category>().Join<categorymap,category, ....
Generally i prefer the lambda syntax with LINQ, but Join
is one example where i prefer the query syntax - purely for readability.
Nonetheless, here is the equivalent of your above query (i think, untested):
var query = db.Categories // source
.Join(db.CategoryMaps, // target
c => c.CategoryId, // FK
cm => cm.ChildCategoryId, // PK
(c, cm) => new { Category = c, CategoryMaps = cm }) // project result
.Select(x => x.Category); // select result
You might have to fiddle with the projection depending on what you want to return, but that's the jist of it.
You can find a few examples here:
// Fill the DataSet. DataSet ds = new DataSet(); ds.Locale = CultureInfo.InvariantCulture; FillDataSet(ds); DataTable contacts = ds.Tables["Contact"]; DataTable orders = ds.Tables["SalesOrderHeader"]; var query = contacts.AsEnumerable().Join(orders.AsEnumerable(), order => order.Field<Int32>("ContactID"), contact => contact.Field<Int32>("ContactID"), (contact, order) => new { ContactID = contact.Field<Int32>("ContactID"), SalesOrderID = order.Field<Int32>("SalesOrderID"), FirstName = contact.Field<string>("FirstName"), Lastname = contact.Field<string>("Lastname"), TotalDue = order.Field<decimal>("TotalDue") }); foreach (var contact_order in query) { Console.WriteLine("ContactID: {0} " + "SalesOrderID: {1} " + "FirstName: {2} " + "Lastname: {3} " + "TotalDue: {4}", contact_order.ContactID, contact_order.SalesOrderID, contact_order.FirstName, contact_order.Lastname, contact_order.TotalDue); }
Or just google for 'linq join method syntax'.
If you have configured navigation property 1-n I would recommend you to use:
var query = db.Categories // source
.SelectMany(c=>c.CategoryMaps, // join
(c, cm) => new { Category = c, CategoryMaps = cm }) // project result
.Select(x => x.Category); // select result
Much more clearer to me and looks better with multiple nested joins.