CODEWITHSUNDEEP
c#asp.net-mvclinq
Javier Perez
Javier Perez

Reputation: 23

Is there a way to make this in linq?

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

Upvotes: 1

Views: 197

Answers (1)

NetMage
NetMage

Reputation: 26907

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();

Upvotes: 2

Related Questions