EntityFramework FromRawSql(top 100) and Where(additional constraints)

David Thielen 2,796 Reputation points
2023-11-30T22:09:03.4066667+00:00

Hi all;

I think this is a SQL Database question. But how Entity Framework constructs a query may make a difference here.

I presently have code that does the following (maxRows == 200):

IQueryable<Organization> query;
var fullTextQuery = $"SELECT top {maxRows} o.* FROM Organizations o INNER JOIN FreeTextTable(Organizations, *, {{0}}, {maxRows}) as t on o.Id = t.[KEY]";
query = dbContext.Organizations.FromSqlRaw(fullTextQuery, queryText);

query = query.Where(o => (!o.Private) || scope.RightsTo.Contains(o.UniqueId));
var userFollowingIds = user.Following.Select(f => f.Id).ToList();
query = query.Where(org => userFollowingIds.Contains(org.Id));

// there are more Where() clauses

query = query.Take(maxRows.Value);
var listOrgs = await query.ToListAsync();

Here's my concern. Does that first SELECT top {maxRows} ... , {maxRows}) reduce what is returned to 200, then the subsequent Where() clauses reduce the count more?

And if so, the call FreeTextTable( ... {maxRows}) is very powerful in that it has SQL Database do the hard work of finding the highest ranked entries most efficiently. Do I have to give that up and have it be FreeTextTable( ... ) (no {maxRows}) add in all the Where() clauses, then finally reduce it to 200?

And if so, is that a big hit? Or will SQL Database optimize if it only has that final query.Take(maxRows.Value)?

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
725 questions
Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,327 questions
0 comments No comments
{count} votes

Accepted answer
  1. Bruce (SqlWork.com) 61,266 Reputation points
    2023-12-01T01:31:28.9666667+00:00

    You can use sqlprofiler to the generated sql, but I don’t believe it does what you want.

    To add a where clause to raw sql, the raw sql is used to generate a derived table clause, then the where applied. As the top is part of the derived table, where will be applied to result off the derived table.

    the proper sql in this case is to append a fetch n rows rather than use top. You can use the Take() to do this. You probably want an OrderBy also.


1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 106.5K Reputation points
    2023-11-30T22:17:18.47+00:00

    I have no idea of what that "query = query.Where(o => (!o.Private)" mumbo-jumbo does, and I am sure that I want to know. SQL is a complex and powerful language in itself, so why bury it in something else, and you end up asking yourself "what is going on here".

    Oh well, I digress. But you could use Profiler to see what is actually being submitted to SQL Server. If you see no trance of that o.Private condition, I guess this means that the filter is applied client-side, and you the actual result set may be less than 200 rows.