How do I get the FreeTextTable.Rank via Entity Framework

Question

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

Answer 1

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.