Entity Framework Core mapping columns to object

Question

I have a string that represents a query. The query is quite complex and it involves multiple joins. Here's what I'm selecting:

SELECT 
    [SO].Id, [SO].SerialNumber, [SO].EstimateId,
    CASE
        WHEN [SO].EstimateId IS NOT NULL 
            THEN (SELECT TOP(1) [EST].[SerialNumber]
                  FROM [Estimates] AS [EST]
                  WHERE ([EST].[OrganizationId] = 1) 
                    AND ([EST].[EstimateId] = [SO].[EstimateId]))
            ELSE NULL
    END, 
    [SO].RecCreateTs, [SO].EstimatedDeliveryDate, [SO].Status, 
    [SO].PriceTotal, [SO].InRevision, [CST].CompanyName, 
    [CON].FirstName, [CON].LastName, [SOR].FirstName, [SOR].LastName, 
    [SOR].EstimatedDeliveryDate, [SOR].PriceTotal

Some columns are identical, but they come from different tables.

I tried to map the properties inside the Select:

var salesOrders = _context.SalesOrders.FromSqlRaw(sqlString)
    .Select(so => new SalesOrder
    {
        Id = so.Id,
        SerialNumber = so.SerialNumber,
        EstimateId = so.EstimateId,
        // the property below is [NotMapped]
        EstimateSerialNumber = so.EstimateId != null ? _context.Estimates.AsNoTracking().FirstOrDefault(w => w.OrganizationId == organizationId && w.EstimateId == so.EstimateId).SerialNumber : null,
        RecCreateTs = so.RecCreateTs,
        EstimatedDeliveryDate = so.EstimatedDeliveryDate,
        Status = so.Status,
        PriceTotal = so.PriceTotal,
        InRevision = so.InRevision,
        Customer = new Customer { CompanyName = so.Customer.CompanyName },
        CustomerContact = new CustomerContact { FirstName = so.CustomerContact.FirstName, LastName = so.CustomerContact.LastName },
        SalesOrderRevisions = so.InRevision
        ? new List<SalesOrderRevision>
        {
            so.SalesOrderRevisions.OrderByDescending(x => x.Id).Select(soRevision => new SalesOrderRevision
            {
                CustomerContact = new CustomerContact { FirstName = soRevision.CustomerContact.FirstName, LastName = soRevision.CustomerContact.LastName },
                EstimatedDeliveryDate = soRevision.EstimatedDeliveryDate,
                PriceTotal = soRevision.PriceTotal
            }).FirstOrDefault()
        }
        : null
    })
    .ToList();

But it didn't work. How can I tell EF Core how to associate each column with the corresponding object property?

Answer 1

Your raw SQL query has some issues, for example:

...[CON].FirstName, [CON].LastName, [SOR].FirstName, [SOR].LastName...

This results in 2 FirstName and 2 LastName, and that confuses EF because it won't know which FirstName are you mapping into SalesOrder.

I'd suggest don't use .FromSqlRaw(sqlString) at all, and use proper LINQ to manipulate with the query. We don't know what SOR or CON or SO or CST is, so further help might be not possible. But try re-write into LINQ query by yourself first.