Entity Framework GroupJoin different result vs join with group

Question

I try to convert this query syntax:

 res = from co in db.SALES
                  join
                   op in db.OPERATORS
                   on co.ID_OP equals op.ID
                  where co.DATA_CLOSE == null
                  group new { co, op } by new { op.ID, op.NAME } into g
                  select new
                  {
                      ID = g.Key.ID,
                      NAME= g.Key.NAME,
                      COUNT = g.Select(x => x.co).Count()

                  };

to method syntax:

res= db.OPERATORS.GroupJoin(
              db.SALES.Where(c=>c.DATA_CLOSE==null),
               op => op.ID,
               co => co.ID_OP,
             (co, grp) =>
             new 
        {
            NAME= co.NAME,
            COUNT    = grp.Select(x=>x.ID ).Count()
             });

first query works as expected,just like inner join,I got only operators with opened sales count second give me ALL operators with opened sales count,if there is no opened sale,it give me 0, how to make second query to work like the first one

thanks

Apparently Join != GroupJoin != GroupBy. Converting a working query to a different syntax make no sense to me. Good luck. — Ivan Stoev
– Ivan Stoev, Commented Apr 30, 2016 at 19:28
Hi,I want to convert some sql joins to method syntax that's what I want to use,I've only succeed to convert them to query syntax — mrapi
– mrapi, Commented May 1, 2016 at 8:10

Ivan Stoev · Accepted Answer · 2016-05-01 09:27:45Z

Syntax doesn't matter - you can do one or the another using either syntax.

The difference is coming from the way the queries are built (mentioned in the post title) - join(Join) + group by(GroupBy) vs join into (GroupJoin).

The former is basically an equivalent of SQL way of doing it. While GroupJoin is a LINQ specific construct. The important thing to note is that it has left outer join semantics (in fact it is used to simulate left outer joins in LINQ). In many regards it's better than SQL way because it avoids the redundant grouping of the result. From the other side it always returns outer records due to left outer join semantics. If you want to filter out the outer records with no matching inner records, you have to do that yourself. For instance, by adding at the end of your second query

.Where(x => x.Count != 0)

Or in general, using the following pattern:

from a in A
join b in B on a.Key equals b.Key into g
where g.Any()
...

Collectives™ on Stack Overflow

Entity Framework GroupJoin different result vs join with group

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related