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<List<AddressPersonCompanyDto>>(addressPersonCompanies);
return new PagedResultDto<AddressPersonCompanyDto>(
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<AdrAddress> addressRepository, IRepository<AdrPersonCompany> personCompanyRepository, IRepository<AdrAddressPersonCompany> addressPersonCompanyRepository, IRepository<AdrTitle> titleRepository, IDbContextProvider<TreasureMapDbContext> dbContextProvider) { _addressRepository = addressRepository; _personCompanyRepository = personCompanyRepository; _addressPersonCompanyRepository = addressPersonCompanyRepository; _titleRepository = titleRepository; _dbContextProvider = dbContextProvider; } public ListResultDto<AddressPersonCompanyDto> 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<AddressPersonCompanyDto>(ObjectMapper.Map<List<AddressPersonCompanyDto>>(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.
