1

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

Javier Perez
  • 23
  • 1
  • 8
  • 1
    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 Oct 02 '19 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 Oct 02 '19 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 Oct 02 '19 at 15:39
  • @Liam then you could use `.FromSql()`, and again query plan caching isn't unique to views is it? – Kieran Devlin Oct 02 '19 at 15:40
  • I'd probably say a Stored proc would make more sense @ZoharPeled or just write some SQL and use a ORM that supports SQL (like Dapper) – Liam Oct 02 '19 at 15:40
  • 1
    The point was "don't bother turning this to **LINQ**" @KieranDevlin – Liam Oct 02 '19 at 15:41
  • `I have tried multiple queries but I'm not too experienced in LINQ and I'm not getting the desired results` please include something we *can replicate* and desired results, if we can't, we are just guessing... – Trevor Oct 02 '19 at 15:50
  • I read the link, I have made attempts but I just started learning LINQ and I'm already using a view since im using 2 tables which one of them is in a different database. – Javier Perez Oct 02 '19 at 15:52
  • My main point was that multiple joins within an ORM can cause performance issues, not that you would necessarily get a performance boost using a view. The view just makes a single new entity for your app to filter and display, vs. trying to join and group multiple objects. Simple queries are fine, some simple joins are fine, but ORMs tend to choke on complexities. – ps2goat Oct 02 '19 at 19:37
  • @ZoharPeled, I would select all the fields you intend to work with and treat the result as a new Entity/object in code. – ps2goat Oct 02 '19 at 19:38
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) could help you. – NetMage Oct 02 '19 at 22:03

1 Answers1

2

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();
NetMage
  • 26,163
  • 3
  • 34
  • 55