.NET Core项目原生SQL执行、LINQ转换及多统计查询求助

Hey there! Let's tackle your question head-on. The short answer is: you can convert this query to LINQ, but it's going to be messy and far less maintainable. Your best bet is to execute the native SQL directly—especially since you don't need to use the ContactRecords entity anyway.

Why LINQ Isn't Ideal Here #

Your query relies on a custom scalar function (usp_Parse_domain_name_v5), multiple distinct counts, conditional left joins, and complex grouping. While EF Core can map custom functions to LINQ, translating all these pieces into readable, correct LINQ would require:

• Registering the custom function in your DbContext
• Manually handling conditional left joins (which don't map cleanly to LINQ's join ... into syntax)
• Ensuring EF Core correctly translates date conversions and aggregations to SQL
• Writing a group-by clause that mirrors your native SQL logic

The end result would be hard to read, debug, and modify—defeating the purpose of using LINQ in the first place.

The Better Approach: Execute Native SQL Directly #

EF Core makes it straightforward to run raw SQL queries and map the results to a strongly-typed DTO (or even anonymous types if you prefer). Here's how to do it:

Step 1: Define a DTO for Your Results

First, create a class to hold the output of your query—this gives you type safety:

public class DomainStatsDto
{
    public string DomainName { get; set; }
    public int TotalImpressions { get; set; }
    public int UniqueImpressions { get; set; }
    public int Conversions { get; set; }
    public string Date { get; set; }
}

Step 2: Execute the Raw SQL Query

Use FromSqlRaw (EF Core 3.0+) to run your native SQL and map it to the DTO. You can parameterize the date to avoid SQL injection:

using Microsoft.EntityFrameworkCore;
using System.Threading.Tasks;

// In your service or repository
public async Task<List<DomainStatsDto>> GetDomainStatsAsync(string targetDate)
{
    var query = @"SELECT 
                      dbo.usp_Parse_domain_name_v5(ContactRecord.URL) AS DomainName, 
                      COUNT(ContactRecord.ContactId) AS TotalImpressions, 
                      COUNT(DISTINCT ContactAttribute.ContactAttributeValue) AS UniqueImpressions, 
                      COUNT(DISTINCT LEADS.LEAD_ID) AS Conversions, 
                      MAX(convert(varchar(10), ContactRecord.CallStartDateTime, 102)) AS Date 
                  FROM ContactAttribute 
                  LEFT JOIN ContactRecord 
                      ON ContactRecord.ContactId = ContactAttribute.ContactId 
                      AND ContactAttribute.ContactAttributeName = 'IP' 
                  LEFT JOIN LEADS 
                      ON LEADS.LEAD_ID = ContactRecord.ContactId 
                      AND LEADS.EMAIL IS NOT NULL 
                      AND LEADS.PHONENUMBER IS NOT NULL 
                      AND LEADS.FIRSTNAME IS NOT NULL 
                      AND LEADS.LASTNAME IS NOT NULL 
                  WHERE convert(varchar(10), ContactRecord.CallStartDateTime, 102) = {0} 
                  GROUP BY dbo.usp_Parse_domain_name_v5(URL) 
                  ORDER BY COUNT(ContactRecord.ContactId) DESC";

    return await _dbContext.Set<DomainStatsDto>()
                           .FromSqlRaw(query, targetDate)
                           .ToListAsync();
}

If You Must Use LINQ (Not Recommended) #

If you have a strict requirement to use LINQ, here's a rough outline of what it would look like—but be warned, this is error-prone and hard to maintain:

1. Register the custom function in your DbContext:

[DbFunction("usp_Parse_domain_name_v5", Schema = "dbo")]
public static string ParseDomainName(string url)
{
    throw new NotImplementedException("This method is only for LINQ query translation");
}

2. Write the LINQ query (note: date handling and join conditions may need adjustment for your EF Core version):

var targetDate = DateTime.Parse("2019-05-16");

var stats = from ca in _dbContext.ContactAttributes
            join cr in _dbContext.ContactRecords 
                on new { ca.ContactId, AttributeName = "IP" } 
                equals new { cr.ContactId, AttributeName = (string)"IP" } into crJoin
            from cr in crJoin.DefaultIfEmpty()
            join lead in _dbContext.Leads 
                on cr.ContactId equals lead.LEAD_ID into leadJoin
            from lead in leadJoin.DefaultIfEmpty()
            where cr != null 
                && EF.Functions.DateDiffDay(targetDate, cr.CallStartDateTime) == 0
                && (lead == null || (lead.EMAIL != null && lead.PHONENUMBER != null && lead.FIRSTNAME != null && lead.LASTNAME != null))
            group new { cr, ca, lead } by DbContext.ParseDomainName(cr.URL) into g
            select new DomainStatsDto
            {
                DomainName = g.Key,
                TotalImpressions = g.Count(x => x.cr.ContactId != null),
                UniqueImpressions = g.Select(x => x.ca.ContactAttributeValue).Distinct().Count(),
                Conversions = g.Select(x => x.lead.LEAD_ID).Distinct().Count(id => id != null),
                Date = g.Max(x => x.cr.CallStartDateTime?.ToString("yyyy.MM.dd"))
            };

var result = await stats.ToListAsync();

Final Recommendation #

Stick with the native SQL approach. It's faster to implement, easier to debug, and exactly mirrors the logic you already know works. LINQ is great for simple to moderate queries, but for complex aggregations with custom functions, raw SQL is the way to go.

内容的提问来源于stack exchange，提问作者Pablo Camara