Get properties from second (Right) joined entity with Group by

user_13491458 0
Viewed 59

please I'm working on a ASP.NET MVC project with Entity Framework. I try to write a Linq Query that give me some data, this Query will join two entities and grouping by data, so the issue is when I try to get the properties of second joined entity I don't see them in Intellisense, I need these properties to select them.

What I try :

var R = (from N in SCHOOL_DB_Context.Con.NT_CTR join S in SCHOOL_DB_Context.Con.STGs on N.STG_nt equals S.CD_STG where N.CTR_nt==CTR group N by N.STG_nt into G select new NT_CTR_Anal { /*Here where I want to select some properties from second entity*/ } )

So please any help about this issue ?

entity-framework asp.net-mvc linq c#
1 Answers

Take a look at tis fragment:

group N by N.STG_nt into G

The part between by and into is the key(s), and similar to SQL there you have access to all aliases (variables) from from and join clauses. The name after into is LINQ specific and represents the alias (variable) for accessing the GroupBy result. But what is between group and 'by'? There is no SQL equivalent.

Well, the result of GroupBy is of type IGrouping<TKey, TElement>, which has property TKey Key and also is IEnumerable<TElement>. The TKey is comong from the expression between by and into, while TElement (which is what you can access through into variable is coming from the expression between group and by.

In your sample you've put N there, that`s why you have access only to its properties.

In order to have access to other properties, you would use the typical LINQ construct for "composite" things, which is anonymous type projection, e.g.

var R = (
    from N in SCHOOL_DB_Context.Con.NT_CTR
    join S in SCHOOL_DB_Context.Con.STGs on N.STG_nt equals S.CD_STG
    where N.CTR_nt == CTR
    group new { N, S } by N.STG_nt into G
    select new NT_CTR_Anal
    {
        G.Key, // N.STG_nt
        SomeNPropSum = G.Sum(e => e.N.SomeNProp),
        SomeSPropSum = G.Sum(e => e.S.SomeSProp),
    };

As you can see, now you have access to both N and S properties inside grouping aggregate methods.

user_52025630
Related