0

I have the following SQL query which I want to translate to EF query

Select Year(_r.IssueDate) as Year, datepart(qq, _r.IssueDate) as Quarter,count(*) as TotalBillableRecords, SUM(_r.BillableHours) as TotalHOurs
FROM
    (select _b.Name, _i.IssueDate, _ie.BillableHours from Beneficiaries _b 
      join [Beneficiaries.Invoices] _i on _b.Id = _i.BeneficiaryId
      join [Beneficiaries.Invoices.Entries] _ie on _ie.InvoiceId = _i.Id
      where BeneficiaryId = 1) as _r
group by Year(_r.IssueDate), datepart(qq, _r.IssueDate)

This results in this

enter image description here

And this is what I would also want to have in my linq expression

How can I translate this to EF query ? I've tried like so

        var query =
           from beneficiary in _dbContext.Beneficiaries
           where beneficiary.Id == beneficiaryId
           from invoice in beneficiary.Invoices
           //where invoice.IssueDate >= since
           //where invoice.IssueDate.Month > notBefore && invoice.IssueDate.Month <= notAfter
           from invoiceEntry in invoice.InvoiceEntries
           group new
           {
               beneficiary,
               invoiceEntry,
           }
           by new
           {
               Year = beneficiary.InvoiceMeta.IssueDate.Year,
               Quarter = (beneficiary.InvoiceMeta.IssueDate.Month - 1)  / 3 + 1,
           }
           into @group
           select new
           {
               Year = @group.Key.Year,
               Quarter = @group.Key.Quarter,
               Hours = @group.Sum(x => x.invoiceEntry.BillableHours),
           };

        var y = query.ToList();

But the result is as following

[0] = Quarter = 3, Year = 2022, Hours = 6729.0

And this is it, only one entry.

What I'm noticing is that it only takes the last quarter composed of 3 months, (because the last would be the one which is not finished yet)

The class hierarchy is

Beneficiary 1-* Invoice 1-* Entries
  • What is "not as expected"? First of all, the SQL and LINQ query are different - even though both group by (year, quarter), the first counts invoices while the second counts entries, so of course the results will be different. Quarter expression in LINQ looks ok and should return the same value as the function in question. – Ivan Stoev Sep 03 '22 at 06:54
  • @IvanStoev, I've added the results I'm getting for each. What I want is to create a linq query which groups by quarter the entries –  Sep 03 '22 at 07:40
  • But your SQL query is counting **invoices**. The LINQ query is counting entries, but has additional conditions in `where` and also additional `join` to `InvoiceEntries`, which do not exist in your SQL query, and both can filter out some results. In other words, you are comparing results of **different** logical queries, so it's normal to get different results. – Ivan Stoev Sep 03 '22 at 07:56
  • Yes, the idea is as following. I have some data within one year, I want to group that year by quarters and sum that date from that quarter so that I can get a quarterly report. This is waht I'm trying to achieve. Will try to simplify the query but I've been strugling with this for more then one week and have no idea how to solve it. –  Sep 03 '22 at 07:57
  • I guess `Count = @gorup.Key.Count()` is a typo, and actually is `Count = @group.Count()`? Other than that, the LINQ query looks fine for what you are trying to achieve. Does your data contain invoices w/o entries? This is one of the possible reasons for getting less results (`from invoiceEntry in invoice.InvoiceEntries` emits `INNER` join). – Ivan Stoev Sep 03 '22 at 08:05
  • Yes the `Count = @gorup.Key.Count()` is a type, and yes I'm executing both sql and linq on same database –  Sep 03 '22 at 08:15
  • Saw the update. The (hopefully) last test - remove (comment out) all `where ...` lines in your LINQ query and just add single `where beneficiary.Id == 1`. Now the results should match, do they? – Ivan Stoev Sep 03 '22 at 08:16
  • I've removed, and updated the linq expression. Now the result seems to sum every quarter but still only one quarter is shown. –  Sep 03 '22 at 08:25
  • Unfortunately, DatePart is not directly supported in DbFunctions: https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.dbfunctions?view=efcore-6.0. But you could translation for custom functions. Check this answer: https://stackoverflow.com/questions/52529454/sqlfunctions-datepart-equivalent-in-ef-core – PEK Sep 03 '22 at 08:25
  • 1
    The very last clarification (I'm running out of ideas). LINQ query is using `beneficiary.InvoiceMeta.IssueDate`while SQL query uses `invoice.IssueDate`, i.e. single(?) date value (LINQ) vs multiple date values (SQL), which probably are different. So, what is `beneficiary.InvoiceMeta.IssueDate` and can it be replaced with `invoice.IssueDate` in LINQ query? – Ivan Stoev Sep 03 '22 at 08:54
  • @IvanStoev, ha yes you are right , I was not paying attention , InvoiceMeta is one-to-one with Beneficiary, while Invoice is in a one-to-many with Beneficiary. They are represented and modeled as owned entities, so there is no dbset for them –  Sep 03 '22 at 15:11
  • Are you so kind to include in question a [Minimal Reproducible Sample](https://stackoverflow.com/help/minimal-reproducible-example) like this one: https://stackoverflow.com/a/73573568/842935 – dani herrera Sep 04 '22 at 16:21

0 Answers0