Is there a way to make this in linq?

Question

I'm getting a sum of the checks that have been printed but haven't been cashed yet by checking 2 tabled in the database thru entitiyframework

I have tried multiple queries but I'm not too experienced in LINQ and I'm not getting the desired results, below is some of my implementations.

select sum(checks.Check_Amount) from dbo.ODC_Register checks left join dbo.vw_BMO_Daily cashed on checks.Check_Number = cashed.DCheckNo where cashed.Id is null and checks.Check_Date < '2019-9-3'

This is what i tried last

 var missing = from checks in Context.ODC_Register where(!Context.vw_BMO_Daily.Where(ma => Convert.ToInt32(ma.DCheckNo) == checks.Check_Number && checks.Check_Date <= ma.DatePosted).Any()) select new {checks.Check_Amount }; var missingSum = missing.Sum( x => x.Check_Amount);

All I need is to find a way to make this into a LINQ query

There are ways to do that, but there's generally better performance if you create a view for this (not including the check date), then pass the check date. This is especially true with more complex joins. Please clarify how you are retrieving the data, though: are you using an ORM like Entity Framework? which one? — ps2goat
– ps2goat, Commented Oct 2, 2019 at 15:34
@ps2goat Why is it better for performance? As far as I'm aware, views don't affect performance, they just simplify implementation? — Kieran Devlin
– Kieran Devlin, Commented Oct 2, 2019 at 15:36
@ps2goat how would you construct such a view? would you select check_date, check_amount...` and do the group by/sum in linq, or would you use a window function? — Zohar Peled
– Zohar Peled, Commented Oct 2, 2019 at 15:39
@Liam then you could use .FromSql(), and again query plan caching isn't unique to views is it? — Kieran Devlin
– Kieran Devlin, Commented Oct 2, 2019 at 15:40
The point was "don't bother turning this to LINQ" @KieranDevlin — Liam
– Liam, Commented Oct 2, 2019 at 15:41

NetMage · Accepted Answer · 2019-10-03 19:52:12Z

While a straight forward translation of your SQL is possible, perhaps using the GroupJoin would be a more LINQ friendly approach:

var ans = (from checks in Context.ODC_Register where checks.Check_Date < new DateTime(2019, 9, 3) join cashed in Context.vw_BMP_Daily on checks.Check_Number equals cashed.DCheckNo into cashedj where !cashedj.Any() select checks.Check_Amount).Sum();

PS Not sure why the range variable for ODC_Register is named checks since it is for one check at a time - I would call it check.

PPS In SQL and LINQ, a not exists test is usually preferable to using an empty left join:

var an2 = (from checks in Context.ODC_Register where checks.Check_Date < new DateTime(2019, 9, 3) where !Context.vw_BMP_Daily.Any(cashed => cashed.DCheckNo == checks.Check_Number) select checks.Check_Amount).Sum();

@JavierPerez FYI, I changed .Count() == 0 to !....Any() as a preferred construct.

Collectives™ on Stack Overflow

Is there a way to make this in linq?

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related