I am trying to create a query that queries an address and person using multiple keywords. There is a person table, an address table and a link table addressPerson (m:n).
The query looks like this:
var searchwords = !input.Filter.IsNullOrEmpty() ? input.Filter.Split(" ") : null;
var query = _addressPersonCompanyRepository
.GetAll()
.Include(a => a.Address)
.Include(p => p.PersonCompany)
.Include(t => t.PersonCompany.Title)
.WhereIf(
!input.Filter.IsNullOrEmpty(),
e => searchwords.All(w => String
.Join(" ", e.PersonCompany.NameCompany, e.PersonCompany.Firstname, e.Address.Street, e.Address.Location)
.Contains(w, StringComparison.InvariantCultureIgnoreCase))
)
//.ToList();
.AsQueryable();
var addressCount = await query.CountAsync();
var addressPersonCompanies = query
.OrderBy(input.Sorting)
//.PageBy(input)
.ToList();
var addressPersonCompanyListDto = ObjectMapper.Map>(addressPersonCompanies);
return new PagedResultDto(
addressCount,
addressPersonCompanyListDto
);
Without the namespace Abp.Linq.Extensions and .ToList() the query works if searchwords contains one or more strings. If the namespace is added because it is needed for .PageBy, I get the following error message:
System.InvalidOperationException
HResult=0x80131509
Nachricht = The LINQ expression 'DbSet<AdrAddressPersonCompany>
.Where(a => __ef_filter__p_0 || !(((ISoftDelete)a).IsDeleted) && __ef_filter__p_1 || (Nullable<int>)((IMustHaveTenant)a).TenantId == __ef_filter__CurrentTenantId_2)
.Join(
outer: DbSet<AdrPersonCompany>
.Where(a0 => __ef_filter__p_3 || !(((ISoftDelete)a0).IsDeleted) && __ef_filter__p_4 || (Nullable<int>)((IMustHaveTenant)a0).TenantId == __ef_filter__CurrentTenantId_5),
inner: a => EF.Property<Nullable<long>>(a, "PersonCompanyId"),
outerKeySelector: a0 => EF.Property<Nullable<long>>(a0, "Id"),
innerKeySelector: (o, i) => new TransparentIdentifier<AdrAddressPersonCompany, AdrPersonCompany>(
Outer = o,
Inner = i
))
.Join(
outer: DbSet<AdrAddress>
.Where(a1 => __ef_filter__p_6 || !(((ISoftDelete)a1).IsDeleted) && __ef_filter__p_7 || (Nullable<int>)((IMustHaveTenant)a1).TenantId == __ef_filter__CurrentTenantId_8),
inner: a => EF.Property<Nullable<long>>(a.Outer, "AddressId"),
outerKeySelector: a1 => EF.Property<Nullable<long>>(a1, "Id"),
innerKeySelector: (o, i) => new TransparentIdentifier<TransparentIdentifier<AdrAddressPersonCompany, AdrPersonCompany>, AdrAddress>(
Outer = o,
Inner = i
))
.Where(a => __searchwords_0
.All(w => string.Join(
separator: " ",
value: new string[]
{
a.Outer.Inner.NameCompany,
a.Outer.Inner.Firstname,
a.Inner.Street,
a.Inner.Location
}).Contains(
value: w,
comparisonType: InvariantCultureIgnoreCase)))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
Quelle = Microsoft.EntityFrameworkCore
I have tested:
-
ToList() without namesspace => work
-
ToList() with namespace => error
-
.AsQueryable() with/without namespace => shows intern error
Is there any workaround or a better way to get results from database with multiple keywords over multiple columns?
I'm using .netCore & JQuery
8 Answer(s)
-
0
I think ef core cannot translate the
Contains(string, StringComparison.InvariantCultureIgnoreCase)method.StringComparison.InvariantCultureIgnoreCase
.WhereIf( !input.Filter.IsNullOrEmpty(), e => searchwords.All(w => String .Join(" ", e.PersonCompany.NameCompany, e.PersonCompany.Firstname, e.Address.Street, e.Address.Location) .Contains(w, StringComparison.InvariantCultureIgnoreCase)) ) -
0
Hi maliming, you are right. But I think the problem also has something to do with the namespace Abp.Linq.Extensions. Running the query ....ToList() without using Abp.Linq.Extensions namespace works as expected. If I add the namespace, there is an error.
On the other side ...AsQueryable() doesn't work with or without the namespace Abp.Linq.Extensions.
-
0
I think the problem also has something to do with the namespace Abp.Linq.Extensions.
This should be related to where the
ToListmethod is called.Please share the corresponding code.
-
0
using Abp.Linq.Extensions; public class AddressAppService : MyAppAppServiceBase, IAddressAppService { public AddressAppService(IRepository addressRepository, IRepository personCompanyRepository, IRepository addressPersonCompanyRepository, IRepository titleRepository, IDbContextProvider dbContextProvider) { _addressRepository = addressRepository; _personCompanyRepository = personCompanyRepository; _addressPersonCompanyRepository = addressPersonCompanyRepository; _titleRepository = titleRepository; _dbContextProvider = dbContextProvider; } public ListResultDto GetAddressPersonCompanies(GetAddressesInput input) { var searchwords = !input.Filter.IsNullOrEmpty() ? input.Filter.Split(" ") : null; var addressPersonCompanies = _addressPersonCompanyRepository .GetAll() .Include(adr => adr.Address) .Include(pers => pers.PersonCompany) .Include(tit => tit.PersonCompany.Title) .WhereIf( !input.Filter.IsNullOrEmpty(), e => searchwords.All(w => String .Join(" ", e.PersonCompany.NameCompany, e.PersonCompany.Firstname, e.Address.Street, e.Address.Location) .Contains(w, StringComparison.InvariantCultureIgnoreCase)) ) .ToList(); return new ListResultDto(ObjectMapper.Map>(addressPersonCompanies)); } }Does that help you?
-
0
Which namespace do you get
.WhereIffrom?Without
Abp.Linq.Expressionsnamespace, I believe.WhereIfis doing an in-memory search that is equivalent to:var addressPersonCompanies = _addressPersonCompanyRepository .GetAll() .Include(adr => adr.Address) .Include(pers => pers.PersonCompany) .Include(tit => tit.PersonCompany.Title) + .ToList() .WhereIf( !input.Filter.IsNullOrEmpty(), e => searchwords.All(w => String .Join(" ", e.PersonCompany.NameCompany, e.PersonCompany.Firstname, e.Address.Street, e.Address.Location) .Contains(w, StringComparison.InvariantCultureIgnoreCase)) ) .ToList();For performance, I suggest storing a pre-calculated
NormalizedSearchTargetproperty:addressPersonCompany.NormalizedSearchTarget = String .Join(" ", e.PersonCompany.NameCompany, e.PersonCompany.Firstname, e.Address.Street, e.Address.Location) .ToUpperInvariant();And then searching would be:
var searchwords = !input.Filter.IsNullOrEmpty() ? input.Filter.Split(" ").Select(w => w.ToUpperInvariant()).ToList() : null;e => searchwords.All(w => e.NormalizedSearchTarget.Contains(w)) -
0
Thanks for reply. Without including Abp.Linq.Expressions namespace, it is Abp.Collections.Extensions.WhereIf().
I will test your suggestion next week. -
0
Can you give me a hint, where to do
addressPersonCompany.NormalizedSearchTarget = String .Join(" ", e.PersonCompany.NameCompany, e.PersonCompany.Firstname, e.Address.Street, e.Address.Location) .ToUpperInvariant(); -
0
You can implement domain event handlers for
PersonCompanyandAddressin which you updateAddressPersonCompany.See https://aspnetboilerplate.com/Pages/Documents/EventBus-Domain-Events#handling-events.
