Improving LINQ query for many-to-many relation

Question

I have a database with the following schema:

Now, I'm trying to pull all landingpages for a domain and sort those by the first UrlFilter's FilterType that matches a certain group. This is the LINQ I've come up with so far:

var baseQuery = DbSet.AsNoTracking()
.Where(e => EF.Functions.Contains(EF.Property<string>(e, "Url"), $"\"{searchTerm}*\""))
.Where(e => e.DomainLandingPages.Select(lp => lp.DomainId).Contains(domainId));

var count = baseQuery.Count();
var page = baseQuery
    .Select(e => new
    {
        LandingPage = e,
        UrlFilter = e.LandingPageUrlFilters.FirstOrDefault(f => f.UrlFilter.GroupId == groupId)
    })
    .Select(e => new
    {
        e.LandingPage,
        FilterType = e.UrlFilter == null ? UrlFilterType.NotCovered : e.UrlFilter.UrlFilter.UrlFilterType
    })
    .OrderBy(e => e.FilterType)
    .Skip(10).Take(75).ToList();

Now, while this technically works, it's quite slow with execution times ranging from 10-30 seconds, which is not good enough for the use case. The LINQ is translated to the following SQL:

SELECT [l1].[Id], [l1].[LastUpdated], [l1].[Url], CASE
    WHEN (
        SELECT TOP(1) [l].[LandingPageId]
        FROM [LandingPageUrlFilters] AS [l]
        INNER JOIN [UrlFilters] AS [u] ON [l].[UrlFilterId] = [u].[Id]
        WHERE ([l1].[Id] = [l].[LandingPageId]) AND ([u].[GroupId] = @__groupId_3)) IS NULL THEN 4
    ELSE (
        SELECT TOP(1) [u0].[UrlFilterType]
        FROM [LandingPageUrlFilters] AS [l0]
        INNER JOIN [UrlFilters] AS [u0] ON [l0].[UrlFilterId] = [u0].[Id]
        WHERE ([l1].[Id] = [l0].[LandingPageId]) AND ([u0].[GroupId] = @__groupId_3))
END AS [FilterType]
FROM [LandingPages] AS [l1]
WHERE CONTAINS([l1].[Url], @__Format_1) AND @__domainId_2 IN (
    SELECT [d].[DomainId]
    FROM [DomainLandingPages] AS [d]
    WHERE [l1].[Id] = [d].[LandingPageId]
)

ORDER BY CASE
    WHEN (
        SELECT TOP(1) [l2].[LandingPageId]
        FROM [LandingPageUrlFilters] AS [l2]
        INNER JOIN [UrlFilters] AS [u1] ON [l2].[UrlFilterId] = [u1].[Id]
        WHERE ([l1].[Id] = [l2].[LandingPageId]) AND ([u1].[GroupId] = @__groupId_3)) IS NULL THEN 4
    ELSE (
        SELECT TOP(1) [u2].[UrlFilterType]
        FROM [LandingPageUrlFilters] AS [l3]
        INNER JOIN [UrlFilters] AS [u2] ON [l3].[UrlFilterId] = [u2].[Id]
        WHERE ([l1].[Id] = [l3].[LandingPageId]) AND ([u2].[GroupId] = @__groupId_3))
END
OFFSET @__p_4 ROWS FETCH NEXT @__p_5 ROWS ONLY

Now my question is, how can I improve the execution time of this? Either by SQL or LINQ

EDIT: So I've been tinkering with some raw SQL and this is what I've come up with:

with matched_urls as (
    select l.id, min(f.urlfiltertype) as Filter
    from landingpages l
    join landingpageurlfilters lpf on lpf.landingpageid = l.id
    join urlfilters f on lpf.urlfilterid = f.id
    where f.groupid = @groupId
    and contains(Url, '"barz*"')
    group by l.id
) select l.id, 5 as Filter
from landingpages l
where @domainId in (
    select domainid
    from domainlandingpages dlp
    where  l.id = dlp.landingpageid
) and l.id not in (select id from matched_urls ) and contains(Url, '"barz*"')
union select * from matched_urls
order by Filter
offset 10 rows fetch next 30 rows only

This performs somewhat okay, cutting the execution time down to ~5 seconds. As this is to be used for a table search I would however like to get it down even further. Is there any way to improve this SQL?

Answer 1

You're right to have a look at the generated SQL. In general, I would advise to learn SQL, write a performing SQL query and work your way back (either use a stored procedure or raw SQL, or design your LINQ query with that same philosophy.

I suspect this will be better (not tested):

var page = (
    from e in baseQuery
    let urlFilter = e.LandingPageUrlFilters.OrderBy(f => f.UrlFilterType).FirstOrDefault(f => f.UrlFilter.GroupId == groupId)
    let filterType = urlFilter == null ? UrlFilterType.NotCovered : e.UrlFilter.UrlFilter.UrlFilterType
    select new 
    {
      LandingPage = e,
      FilterType = filterType
    }
).Skip(10).Take(75).ToList();
    

Answer 2

one of the way to improve the execution time is see execution plan in SSMS (SQL Server Management Studio).

After look on the execution plan you can design some indexes, or if you have no experiences with this, you can see if SSMS recommends some indexes.

Next try to create the indexes and execute the query again and see if execution time was improved.

Note: this is only one of many possible ways to improve execution time...