Convert SQL request to LINQ

Question

I have an object like this:

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 Email { get; set; }
        public string Language { get; set; }
}

I also have a database SQL query (a table of the above objects) that returns some results:

SELECT 
    DocumentNumber, DocumentDate, DocumentReference,
    SerialNumber, ProductCode, Description, Certificate
FROM 
    vInvoice
WHERE 
    CHECKSUM(DocumentNumber + DocumentReference + CAST(DocumentDate AS VARCHAR)) 
        IN (SELECT CHECKSUM(DocumentNumber + DocumentReference + CAST(DocumentDate AS VARCHAR))
            FROM vInvoice
            WHERE Email = 'somemail@gmail.com' AND Language = 'FR'
            GROUP BY DocumentNumber, DocumentDate, DocumentReference
            ORDER BY DocumentDate 
                OFFSET 0 ROWS
                FETCH NEXT 4 ROWS ONLY)

Can I write a LINQ request identical to the above SQL statement?

Will your Linq query talk directly to the database, or be executed against an in-memory collection already retrieved from the database? — Robert Harvey, Feb 06 '20 at 20:14
If you want it to retrieve the result from the database directly, why not execute a direct query, since you already have the SQL and you already know it works? See https://learn.microsoft.com/en-us/ef/core/querying/raw-sql — Robert Harvey, Feb 06 '20 at 20:16
@RobertHarvey, the fact is that this SQL request returns a modified object `Invoice` (without fields `Email` & `Langage`) and I get an error. see https://stackoverflow.com/questions/60101749/sql-query-fails-in-ef-core — Роман Тимохов, Feb 06 '20 at 20:25
@РоманТимохов can you try to add the `Email` and `Language` to the sql select as empty values, as they are strings you will be able to see if thats the problem. which I dont think so. Are you trying to map directly to the `invoice` object? also which connector are you using for sql? is that SqlServer or MySQL; with this weird queries/errors it may be important. — TiGreX, Feb 06 '20 at 20:29
what about to use https://github.com/linq2db/linq2db syntaxis is very similar — Serhii Matvienko, Feb 06 '20 at 20:38
@TiGreX i use `SqlServer` connector. By `Email` and `Language` i filte this objects from DB — Роман Тимохов, Feb 06 '20 at 20:42
It might be worth your time to fix your query in that other question by adding the `Email` field into the SELECT statement, as the error message suggests. — Robert Harvey, Feb 06 '20 at 20:43
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Feb 06 '20 at 22:38

score 0 · Answer 1 · answered Feb 06 '20 at 22:32

Ok, so what I'd be doing in this situation is putting the view into a DBML or EDMX and then using linq in a function to build a list of matching invoices. The code itself is pretty straight forward, but looking at your query I have to ask: what is the purpose of the CHECKSUM?

To me it looks like you're just grabbing all of the invoices that match the email and language and could simply have your query be:

SELECT TOP 4
    [DocumentNumber], 
    [DocumentDate], 
    [DocumentReference],
    [SerialNumber], 
    [ProductCode], 
    [Description], 
    [Certificate]
FROM 
    [vInvoice]
WHERE [Email] = 'somemail@gmail.com' AND [Language] = 'FR'
GROUP BY [DocumentNumber], [DocumentDate], [DocumentReference]
ORDER BY [DocumentDate]

What I would do for a C# function is something like the following:

public IList<Invoice> GetInvoices(string email, string language)
{
    Using (EntityContext context = new EntityContext(ConnectionStrings.Connection))
    {
        IList<Invoice> results;
        results = context.vInvoices
            .Where(x => x.Email == email && x.Language == language)
            .Select(x => new Invoice
            {
                DocumentNumber = x.DocumentNumber, 
                DocumentDate = x.DocumentDate, 
                DocumentReference = x.DocumentReference,
                SerialNumber = x.SerialNumber, 
                ProductCode = x.ProductCode, 
                Description = x.Description, 
                Certificate = x.Certificate,
                Email = x.Email,
                Language = x.Language
            })
            .OrderBy(x => x.DocumentDate)
            .ToList();

        return results;
    }
}

Convert SQL request to LINQ

1 Answers1