CODEWITHSUNDEEP
linqlinq-to-sqlentity-framework-corelinq-to-entities
Роман Тимохов
Роман Тимохов

Reputation: 418

Correct interpretation of SQL request by EF Core

I have a certain table in the database that stores the following objects:

public partial class Invoice
{
    public string DocumentNumber { get; set; }
    public DateTime? DocumentDate { get; set; }
    public string DocumentReference { get; set; }
    public string SerialNumber { get; set; }
    public string ProductCode { get; set; }
    public string Description { get; set; }
    public string Certificate { get; set; }
    public string Language { get; set; }
    public string Email { get; set; }
}

I also have a query that returns me the number of specific elements:

SELECT Count(*)
        FROM (
                SELECT DocumentNumber,DocumentDate,DocumentReference
                FROM vInvoiceSwivelInfoWeb
                WHERE Email = '[email protected]' AND Language = 'FR'
                GROUP BY DocumentNumber,DocumentDate,DocumentReference
            ) AS T

The answer looks something like this: enter image description here

How to use EF to make such a request and get a numerical answer? I tried like this:

_context.Database.ExecuteSqlRawAsync($"..some SQL query..")

but I do not get the expected result.

UPD: Having received the answer about the impossibility of fulfilling this request through EF, the following question reasonably arose: Is it possible to make this request using LINQ?

Upvotes: 1

Views: 138

Answers (1)

AliReza Sabouri
AliReza Sabouri

Reputation: 5215

You can Leverage ADO.NET via the Context.Database property. Unfortunately, there is no way to get the count from the database using EF Core execute methods if you have a custom query that is not related to your entities.

using (var command = context.Database.GetDbConnection().CreateCommand())
{
    command.CommandText = "SELECT Count(*) From Table1";
    context.Database.OpenConnection();
    using (var result = command.ExecuteReader())
    {
        // do something with result
    }
}

for Updated question

    var count = from a in _context.vInvoiceSwivelInfoWeb
                where a.Email == "[email protected]" && a.Language == "FR"
                group new { a.DocumentNumber , a.DocumentReference , a.DocumentDate } by a into g
                select g.Count()

also, it's important to know which version of EF-Core are you using: currently, if you are using EF-Core 3 group-by doesn't translate to SQL command so you have to do it on client-side:

check this link : https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client

for EF-Core 3.0 - 3.1.1

var count = _context.vInvoiceSwivelInfoWeb
                 .Where(a => a.Email == "[email protected]" && a.Language == "FR" ).ToList()
                 .GroupBy(a => new { a.DocumentNumber ,a.DocumentDate, a.DocumentReference }).Count();

Upvotes: 1

Related Questions