SearchChildren With EFCore

[jasonhill]

I have tried using SearchChildren with EFCore and a query against a SQL database but it doesn't seem to be applying any filters in the generated SQL query.

I saw an old blog post that indicated this might only be available with in-memory queries - is that still the case?

[simbrams]

I'm running into the same problem here, it seems like it has no effect..

[ninjanye]

Hi @jasonhill

Thanks for raising this issue. The SearchChildren functionality should translate to the data source, for example

var shops = context.Shops.SearchChildren(s => s.Products)
                         .With(p => p.Name)
                         .Containing("ball");

should produce sometihng similar to

SELECT [Extent1].[Id] AS [Id]
  -- Additional columns --        
FROM [dbo].[Shops] AS [Extent1]
WHERE EXISTS (
  SELECT 1 AS [C1]
  FROM [dbo].[Products] AS [Extent2]
  WHERE ([Extent1].[Id] = [Extent2].[Shop_Id])
    AND ([Extent2].[Name] LIKE N'%ball%')
)

Do you still have the example you are having trouble with?

[deimosowen]

@ninjanye Hi, yes, it doesn't work

var q = _context.DServicesDocuments
                .Where(w => !documentType.HasValue || w.DServices.SDocumentId == documentType)
                .Select(s => new DocumentsModel
                {
                    Id = s.Id,
                    Name = s.DServices.SDocument.DocumentName,
                    Description = s.Description,
                    OrganizationName = s.DServices.DFolder.SOrganization.OrganizationName,
                    Folder = s.DServices.DFolder.FolderName,
                    Service = s.DServices.CaseNumber,
                    YearStart = s.DServices.YearStart,
                    YearStop = s.DServices.YearStop,
                    Metadata = s.DServicesMetadata.Select(ss => new DocumentMetadata
                    { Name = ss.MetadataName, Value = ss.MetadataValue })
                }).AsNoTracking()
                .SearchChildren(s => s.Metadata)
                .With(s => s.Value.ToLower())
                .Containing(query.ToLower().Split(" "))
                .ToQueryString();

SQL

SELECT d.id, s.document_name, d.description, s0.organization_name, d1.folder_name, d0.case_number, d0.year_start, d0.year_stop, d0.id, s.id, d1.id, s0.id, d2.metadata_name, d2.metadata_value, d2.id
FROM d_services_document AS d
INNER JOIN d_services AS d0 ON d.d_services_id = d0.id
INNER JOIN s_document AS s ON d0.s_document_id = s.id
INNER JOIN d_folder AS d1 ON d0.d_folder_id = d1.id
INNER JOIN s_organization AS s0 ON d1.s_organization_id = s0.id
LEFT JOIN d_services_metadata AS d2 ON d.id = d2.d_services_document_id
ORDER BY d.id, d0.id, s.id, d1.id, s0.id, d2.id