Using 8.0 Database.SqlQuery() to call FreeTextTable()

[DavidThielen]

My basic use case is as follows. I have a somewhat complex model Event that has 1:1 and 1:many relationships with other models. It also has several 1:1 relationships where I use builder.OwnsOne() so that the property object is actually additional columns in the Event model.

What I need to do is call FreeTextTable(Events, *, 'my query', 200) and get back the list of Event objects plus the RANK for each as calculated by FreeTextQuery(). In the Event class I have:

[NotMapped]
public int Rank { get; set; }

In addition, I want to add some Where() clauses on the events selected. For example Where(e => e.Enabled). What is the best way to do this?

I have it working where I put the where clauses in the query passes to SqlQuery() and I get back a list of records I defined for the call that have a KEY and RANK property. That works.

If possible I'd like to get back a list of Event objects, with the Rank property populated with the rank from FreeTextQuery.

If that's not possible, then get a query that returns my simple KEY/RANK objects, but I can apply Where(e => e.Enabled) to that query.

And best of all is if there is now support for FreeTextTable in EF where it can give me the results and I don't have to use SqlQuery().

So... any suggestions?

thanks - dave

ps - I tried calling

var listRanks = await dbContext.Database.SqlQuery<Event>(
	$"select * from FreeTextTable(Events, *, {Query}, {MaxRows}) as t INNER JOIN Events u on u.Id = t.[KEY]")
	.Where(e => e.Enabled)
	.ToListAsync();

and got the error:

System.InvalidOperationException: The property 'Event.Address' of type 'Address' appears to be a navigation to another entity type. Navigations are not supported when using 'SqlQuery". Either include this type in the model and use 'FromSql' for the query, or ignore this property using the '[NotMapped]' attribute.
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.<ValidatePropertyMapping>g__Validate|7_0(IConventionTypeBase typeBase, <>c__DisplayClass7_0&)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidatePropertyMapping(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.SqlServer.Infrastructure.Internal.SqlServerModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelRuntimeInitializer.Initialize(IModel model, Boolean designTime, IDiagnosticsLogger`1 validationLogger)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.AdHocMapper.AddEntityType(Type clrType)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.AdHocMapper.GetOrAddEntityType(Type clrType)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.SqlQueryRaw[TResult](DatabaseFacade databaseFacade, String sql, Object[] parameters)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.SqlQuery[TResult](DatabaseFacade databaseFacade, FormattableString sql)
   at LouisHowe.web.Pages.User.Search.SearchClick() in C:\Git\LouisHowe\LouisHowe.web\Pages\User\Search.razor.cs:line 343
   at LouisHowe.web.Pages.User.Search.SearchClick() in C:\Git\LouisHowe\LouisHowe.web\Pages\User\Search.razor.cs:line 374
   at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion(Task task)
   at DevExpress.Blazor.Internal.DxButtonInternal.DoClick(MouseEventArgs mouseArgs)
   at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion(Task task)
   at Microsoft.AspNetCore.Components.RenderTree.Renderer.GetErrorHandledTask(Task taskToHandle, ComponentState owningComponentState)

[ajcvickers]

We discussed this in triage, and I believe it needs at least one of the following to be implemented before this will be possible:

• Enable flexible FromSql mapping #21627
• Add complex expression support for DbFunction parameters #20093

Until then, I don't think it is possible to do all this in a single query from EF.

[DavidThielen]

I have it working for me using the ver 8 pre release

var listRanks = await dbContext.Database.SqlQuery<FullTextTable>($"select * from FreeTextTable(Events, *, {queryText})").ToListAsync();

The above is not great because I have to do a distinct search for the actual event objects (also using FreeTextTable in it) to then assign the Rank properties from the above select to the matching search result of the event objects.