CODEWITHSUNDEEP
c#sql-serverentity-frameworklinq
Nate58
Nate58

Reputation: 93

Linq Multiple Joins

I have some sql tables that I need to query information from my current query that returns a single column list is:

from f in FactSales
where f.DateKey == 20130921
where f.CompanyID <= 1
join item in DimMenuItems
on f.MenuItemKey equals item.MenuItemKey
join dmi in DimMenuItemDepts
on item.MenuItemDeptKey equals dmi.MenuItemDeptKey
group f by dmi.MenuItemDeptKey into c
select new {
Amount = c.Sum(l=>l.Amount)
}

This returns the data I want and it groups correctly by the third table I join but I cannot get the Description column from the dmi table. I have tried to add the field

 Description = dmi.Description

but it doesnt work. How can I get data from the third table into the new select that I am creating with this statement? Many thanks for any help.

Upvotes: 0

Views: 265

Answers (1)

Aron
Aron

Reputation: 15772

Firstly you are using Entity Framework COMPLETELY WRONG. Linq is NOT SQL.

You shouldn't be using join. Instead you should be using Associations.

So instead, your query should look like...

from sale in FactSales
where sale.DateKey == 20130921
where sale.CompanyID <= 1
group sale by sale.Item.Department into c
select new 
{
    Amount = c.Sum(l => l.Amount)
    Department = c.Key
}

By following Associations, you will automatically be implicitly joining.

You should not be grouping by the id of the "table" but by the actual "row", or in Object parlance (which is what you should be using in EF, since the raison d'etre of an ORM is to convert DB to Object), is that you should be grouping by the "entity" rather than they the "entity's key".

EF already knows that the key is unique to the entity.

The grouping key word only allows you to access sale and sale.Item.Department after it. It is a transform, rather than an operator like in SQL.

Upvotes: 1

Related Questions