如何在EF Core中结合Include与FromSql避免生成子查询？

Great question! The subquery you're seeing is EF Core's default behavior when using FromSql alongside Include: it treats your raw SQL (with the WHERE CONTAINS clause) as a subquery, then adds the LEFT JOIN for the Address navigation property on top of that.

To generate the clean query you want (with the WHERE clause after the JOIN), you have two solid options:

Option 1: Use a Custom DbFunction (Maintainable, LINQ-First Approach) #

This lets you keep using LINQ and Include while letting EF Core generate the correct SQL. We'll create a custom DbFunction to wrap the CONTAINS(*, ...) call (which targets all full-text indexed columns in the Companies table).

First, add the function to your DbContext:

public class YourDbContext : DbContext
{
    public DbSet<Company> Companies { get; set; }
    public DbSet<Address> Addresses { get; set; }

    // Custom function for full-text search across all Company columns
    [DbFunction("CONTAINS", "")]
    public static bool SearchAllCompanyColumns(string searchTerm)
    {
        throw new NotImplementedException("This is only for EF Core query translation.");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure EF to translate our function to the SQL CONTAINS(*, ...)
        modelBuilder.HasDbFunction(typeof(YourDbContext).GetMethod(nameof(SearchAllCompanyColumns)))
            .HasTranslation(args =>
                new SqlFunctionExpression(
                    "CONTAINS",
                    new[] 
                    { 
                        new SqlFragmentExpression("*"), // Targets all full-text columns
                        args[0] // The search term parameter
                    },
                    typeof(bool),
                    null));
    }
}

Now use it in your query:

var fullTextSearchVal = "test";
var searchTerm = $"\"*{fullTextSearchVal}*\""; // Wrap in quotes for exact wildcard match

var result = _context.Companies
    .Include(c => c.Address)
    .Where(c => YourDbContext.SearchAllCompanyColumns(searchTerm))
    .ToList();

This will generate exactly the SQL you want:

SELECT [c].[Id], [c].[AddressId], [c].[Email], [c].[Name], [c].[PhoneNumber], [c.Address].[Id], [c.Address].[Address], [c.Address].[City], [c.Address].[Country], [c.Address].[ZipCode]
FROM [Companies] AS [c]
LEFT JOIN [Address] AS [c.Address] ON [c].[AddressId] = [c.Address].[Id]
WHERE CONTAINS(*, N'"*test*"')

Option 2: Raw SQL with JOIN (Quick Fix, Less Maintainable) #

If you prefer to skip the custom DbFunction, you can write the full raw SQL query including the JOIN and WHERE clause. EF Core will populate the Address navigation property automatically if you alias the columns correctly:

var fullTextSearchVal = "test";
var sql = $@"
SELECT 
    [c].[Id], [c].[AddressId], [c].[Email], [c].[Name], [c].[PhoneNumber],
    [a].[Id] AS [Address_Id], [a].[Address] AS [Address_Address], 
    [a].[City] AS [Address_City], [a].[Country] AS [Address_Country], 
    [a].[ZipCode] AS [Address_ZipCode]
FROM [Companies] AS [c]
LEFT JOIN [Address] AS [a] ON [c].[AddressId] = [a].[Id]
WHERE CONTAINS([c].*, '""*{fullTextSearchVal}*""')";

var result = _context.Companies.FromSqlInterpolated(sql).ToList();

Note: This approach requires updating the raw SQL if your Company or Address models change, so it's less maintainable than the DbFunction method.

Why the Original Code Produces a Subquery #

When you use FromSql with Include, EF Core treats your raw SQL as a subquery source for the Companies entities. It then adds the LEFT JOIN to Address on top of that subquery to satisfy the Include request. By moving the WHERE clause into the LINQ query (via the custom DbFunction), EF Core can merge it into the main query with the JOIN.

内容的提问来源于stack exchange，提问作者Ігор Маркевич