How to groupby with inner join using linq lamba expression

Question

I'm trying to convert a sql stored proc to linq. I'm having issues with the groupby and inner joins.

Here is what I've tried:

var r = _context.Table1
    .GroupBy(x => new { x.OptionId, x.Years, x.Strike })
    .Join(_context.Table2,
        oc => oc.OptionId, o => o.OptionId, (oc, o) => new
        {
            OptionsCosts = oc,
            Options = o
        }).Where(x => x.Options.OptionType == 1
                && x.Options.QualifierId != null
                && x.Options.CreditingMethod != "xxx")
    
    .Select(y => new DataModel.Table1()
    {
        Years = y.Select(a => a.OptionsCosts.Years).FirstOrDefault(),
        Strike = y.Select(a => a.OptionsCosts.Strike).FirstOrDefault(),
        Value = y.Select(a => a.OptionsCosts.Value).FirstOrDefault(),
        ChangeUser = y.Select(a => a.OptionsCosts.ChangeUser).FirstOrDefault(),
        ChangeDate = DateTime.Now,
        OptionId = y.Select(a => a.OptionsCosts.OptionId).FirstOrDefault()
    });

Here is the SQL that I'm trying to convert:

SELECT o2.OptionId, o2.Years, o2.Strike, SUM(d2.Weights) as 'TotalWeight', COUNT(*) as 'Counts'
FROM Table1 o2
INNER JOIN @Dates d2  --this is a temp table that just holds dates. I was thinking just a where statement could do it???
  ON d2.EffectiveDate = o2.EffectiveDate
INNER JOIN Table2 od2
   ON od2.OptionId = o2.OptionId
  AND od2.OptionType = 1
  AND od2.qualifierid is null
  AND od2.CreditingMethod <> 'xxx' --28095
GROUP BY o2.OptionId,o2.Years, o2.Strike

My data is way off so I'm sure I'm doing something wrong.

Whichever it is, LINQ isn't a replacement for SQL. It's the ORM's job to generate JOINs from the relations between entities. An ORM is meant to Map Objects to Relational tables and views, not execute arbitrary SQL queries. Table variables are way out of scope of any ORM. — Panagiotis Kanavos, Oct 02 '20 at 09:03
Try write first `join` and after `GroupBy`. In `Select`, you can use `Sum` and `Count` methods. — vernou, Oct 02 '20 at 09:04
Instead of trying to rewrite the SQL query with LINQ, create a view or function and map your objects to it. You can use `FromSqlRaw` or `FromSqlInterpolated` to use a SQL query as a starting point for a LINQ query. The table variable is a problem - where does it come from, how is it created? — Panagiotis Kanavos, Oct 02 '20 at 09:06
Well, take a look at your query: `&& x.Options.QualifierId != null` and SQL: `AND od2.qualifierid is null`. DO you see the difference? — Maciej Los, Oct 02 '20 at 09:09
Agree with @PanagiotisKanavos. But still, use the correct tags :-) — jeroenh, Oct 02 '20 at 09:10
I'd change the order of methods used in linq query: 1. `join`, 2. `where`, 3. `groupby`, 4. `select`. — Maciej Los, Oct 02 '20 at 09:13
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you? Also, LINQPad is a useful tool. — NetMage, Oct 02 '20 at 18:10

score 0 · Answer 1 · answered Oct 04 '20 at 15:44

var table1=_context.Table1
.groupBy(o2=> new{
o2.OptionId
, o2.Years
, o2.Strike
})
.select(s=> new{
s.key.OptionId
, s.key.Years
, s.key.Strike
,TotalWeight=s.sum(x=>x.Weights)
,Counts=o2.count(c=>c.OptionId)
}).tolist();
var result=table1
.Join(_context.Table2,oc => oc.OptionId, o => o.OptionId, (oc, o) => new{ OptionsCosts = oc,  Options = o })
.Where(x => x.Options.OptionType == 1
                && x.Options.QualifierId != null
                && x.Options.CreditingMethod != "xxx")
.select(x=> new {
    x.oc.OptionId,  x.oc.Years,  x.oc.Strike,  x.oc.TotalWeight, x.oc.Counts
}).tolist();

Svyatoslav Danyliv · Accepted Answer · 2020-10-07T18:25:48.677

-1

Small advise, when you rewriting SQL queries, use LINQ Query syntax which is close to SQL and more effective to avoid errors.

var dates = new List<DateTime>() { DateTime.Now }; // fill list

var query = 
    from o2 in _context.Table1
    where dates.Contains(o2.EffectiveDate)
    from od2 in _context.Table1.Where(od2 => // another way to join
         od2.OptionId == o2.OptionId
      && od2.OptionType == 1
      && od2.qualifierid == null
      && od2.CreditingMethod != "xxx")
    group o2 by new { o2.OptionId, o2.Years, o2.Strike } into g
    select new 
    {
       g.Key.OptionId, 
       g.Key.Years, 
       g.Key.Strike, 
       Counts = g.Count()
       // SUM(d2.Weights) as 'TotalWeight', -this one is not available because dates in memory
    };

If you are on start and trying to rewrite procedures on LINQ - EF Core is bad idea. Too limited IQueryable support and usually you will fight for each complex LINQ query. Try linq2db which has temporary tables support and your stored proc can be rewritten into identical LINQ queries. Or you can use linq2db.EntityFrameworkCore to extend EF Core functionality.

Disclaimer. I’m creator of this extension and one from linq2db creators.

edited Oct 07 '20 at 18:25

answered Oct 02 '20 at 10:26

Svyatoslav Danyliv

21,911
3
16
32

Not sure what "more error proven" is attempting to say, but there is no difference between query and fluent/lambda syntax functionally. – NetMage Oct 02 '20 at 18:08
Less chance to make an error. Not error exactly but it limits ways to create non transleable LINQ Queries. – Svyatoslav Danyliv Oct 02 '20 at 20:33
I'm not sure that's true, (you can generate `GroupJoin` and `SelectMany` with query syntax), but there are certainly fewer queries possible in query syntax. – NetMage Oct 02 '20 at 20:41
With query syntax you can generate Where, SelectMany, Join, GroupJoin, OrderBy, GroupBy - it is very verbatim constructions especially if you have several joins and grouping at the end. Compiler do dirty job to create transparent classes. For sure there is First, Distinct, Concat, Union, etc. which do not work with query syntax, so rational mixing is needed. Anyway query syntax is more strict to prevent issues with query translation to SQL and it is better readable from SQL perspective. Any query syntax can be converted to lambda syntax, but this false in back conversion. – Svyatoslav Danyliv Oct 03 '20 at 13:36
What lambda syntax using supported operations do you think can't be translated to query syntax? – NetMage Oct 05 '20 at 18:46
Using join, especially this "other way", should never be encouraged. Always use navigation properties if possible. *That* is a way to avoid errors. The question doesn't give any reason to believe a priori that a navigation property can't be used. Also, I don't see the added value of this library you plug here without mentioning your affiliation. – Gert Arnold Oct 06 '20 at 07:28
@GertArnold, if you can create effective LINQ query without this extension, any solutions are welcome. Added disclaimer. – Svyatoslav Danyliv Oct 06 '20 at 07:47

How to groupby with inner join using linq lamba expression

2 Answers2