How do I get the FreeTextTable.Rank via Entity Framework

David Thielen 2,306 Reputation points
2023-10-01T21:17:12.57+00:00

Hi all;

I'm able to get a list of records using FreeTextTable in Entity Framework as follows:

var EventsSqlWhere = $"SELECT top 200 * FROM FreeTextTable(Events, *, {0}, 200) as t INNER JOIN Events u on u.Id = t.[KEY] ORDER BY t.[RANK] desc";

listEvents = await dbContext.Events
        .FromSqlRaw(EventsSqlWhere, Query)
        .Where(e => (! e.Private) && (e.Enabled))
        .Include(e => e.Signups)!
        .ThenInclude(s => s.User)
        .ToListAsync();

But nothing I try can also get me the t.RANK column. How can I get that? Either as a [NotMapped] column in the Event object or the returned list objects are a tuple<Event,Rank>. Or anything.

??? - thanks - dave

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

1 answer

Sort by: Most helpful
  1. Hui Liu-MSFT 41,146 Reputation points Microsoft Vendor
    2023-10-02T06:59:38.75+00:00

    Hi,@David Thielen. Welcome Microsoft Q&A. If you want to include columns that are not part of the entity class, such as the t.[RANK] column from your query, you could try to project the results into an anonymous type or a custom DTO (Data Transfer Object) class.

    Here's how you can project the results into an anonymous type:

    var EventsSqlWhere = $"SELECT top 200 *, t.[RANK] as Rank FROM FreeTextTable(Events, *, {0}, 200) as t INNER JOIN Events u on u.Id = t.[KEY] ORDER BY t.[RANK] desc";
    
    var listEventsWithRank = await dbContext.Events
        .FromSqlRaw(EventsSqlWhere, Query)
        .Where(e => (!e.Private) && (e.Enabled))
        .Include(e => e.Signups)
        .ThenInclude(s => s.User)
        .Select(e => new
        {
            Event = e,
            Rank = e.Rank // Assuming there is a Rank property in the Event entity
        })
        .ToListAsync();
    
    

    You modify the SQL query to include t.[RANK] as Rank in the SELECT statement, aliasing it as Rank.

    You use the Select method to project the results into an anonymous type that contains both the Event object and the Rank.

    The Rank property of the anonymous type is populated with the value from the t.[RANK] column.

    ListEventsWithRank will be a list of anonymous objects, each containing both the Event entity and the Rank value. You can access the Rank property as needed.

    If I understand something wrong or the problem is not solved, please let me know.


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.