Provide a way to return a joined result

[DavidThielen]

I want to query using FreeTextTable() where I get back a join of my model and the FreeTextTable.Rank. I have the following query:

`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();

I'd like to be able to do a Select() that returns a tuple of the model class (Event in my example) and the FreeTextTable.Rank value.

And obviously, solve this for the general case of the returned join has a model class and other columns and so return the model object and the other column values.

[ajcvickers]

@DavidThielen It looks like you are doing something similar to this: #21888, but it's a bit hard to tell. Can you post:

• The SQL you expect to get generated. including the parts generated by EF (if any)
• The shape of the results returned

[DavidThielen]

@ajcvickers Thanks for the link. I think #10753 will provide this functionality. Have to wait for version 8.0 but then we'll have it.

[roji]

Duplicate of #10753

[roji]

@DavidThielen give 8.0.0-rc.2 a try - this is a very stable version with a go-live license; it will likely be very close to the final 8.0 version that will be released in November.

[DavidThielen]

@roji Where can I find an example or documentation on how to use this. Happy to try the RC, but need to know how and couldn't find an example of how this works. TIA.

[roji]

@DavidThielen see https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/whatsnew#raw-sql-queries-for-unmapped-types

[DavidThielen]

@roji Thanks for the link. I'm using it now and see a road to success. I'm trying to figure out the best approach so if you have any suggestions, they would be much appreciated. #32066

thanks - dave