Adding namespace Abp.Linq.Extensions results in error on query #8577 | Support Center

geoteam created 6 years ago

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
maliming created 6 years ago
Support Team
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)) )

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)
0

geoteam created 6 years ago

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.

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)
0

maliming created 6 years ago
Support Team

I think the problem also has something to do with the namespace Abp.Linq.Extensions.

This should be related to where the ToList method is called.

Please share the corresponding code.

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)

geoteam created 6 years ago

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?

aaron created 6 years ago

Support Team

Which namespace do you get .WhereIf from?

Without Abp.Linq.Expressions namespace, I believe .WhereIf is 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 NormalizedSearchTarget property:

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

geoteam created 6 years ago

Thanks for reply. Without including Abp.Linq.Expressions namespace, it is Abp.Collections.Extensions.WhereIf(). I will test your suggestion next week.

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)

geoteam created 6 years ago

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

aaron created 6 years ago
Support Team

You can implement domain event handlers for PersonCompany and Address in which you update AddressPersonCompany.

See https://aspnetboilerplate.com/Pages/Documents/EventBus-Domain-Events#handling-events.

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)

Have an answer to this question? Log in and write your answer.