Base solution for your next web application
Open Closed

EF Core 3.x breaking existing EF Core 2.x query #8843


User avatar
0
Ricavir created

Hi,

I've upgraded my aspnetzero project to .NETCORE 3.1 and ABP 5.5.

Due to EF Core breaking changes, I've needed to rewrite some queries.

I didn't manage to convert one of them that is using an EF value converter to convert an Enumerable<string> (in EF side) to a string (in SQL Server side).

The query I want to convert is that one :

var result= _myRepository.Where(a => a.TagList.Any(t => tagSearch.Contains(t)))

The aggregation of Any() and Contains() in this query is problematic and is not being translated by EF.

I've tried to wrote SQL query directly from dbcontext with FromSqlRaw method but I don't manage to build this query...

Can you please provide some help ?

I've asked a question on stackoverflow too, here is the link to it : https://stackoverflow.com/questions/61123023/convert-ef-core-query-in-raw-sql


11 Answer(s)
  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi @ricavir,

    Did you check EF Core's GitHub repo ? I think they can provide a solution to this. If you can share your entity and what is tagSearch, we can also try to help.

  • User Avatar
    0
    Ricavir created

    Hi @ismcagdas,

    Yes I check github repo for my other issues. I've fixed some queries by querying directly with SQL...

    So here is the entity :

        [Table("LsAddresses")]
        public class Address : FullAuditedEntity<long>, IMustHaveTenant, IMustHaveSuccessiveId<long>
        {
            public virtual int TenantId { get; set; }
    
            public virtual long SuccessiveId { get; set; }
    
            [MaxLength(ConstForLength.Name)]
            public virtual string Name { get; set; }
    
            [MaxLength(ConstForLength.DescriptionTitle)]
            public virtual string Title { get; set; }
    
            public virtual IEnumerable<string> TagList { get; set; }
            
            ....
    

    TagList property is converted in database with value converter :

                var splitStringConverter = new ValueConverter<IEnumerable<string>, string>(v => string.Join(";", v), v => v.Split(new[] { ';' }));
                //Tag list database converter
                modelBuilder.Entity<Address>().Property(nameof(Address.TagList)).HasConversion(splitStringConverter);
    

    As a consequence, TagList is stored in table column in a string with ';' delimiter.

    The query in related appservice, including TagSearch, is this one : (TagSearch is string with ';' delimiter)

                    var addresses = _addressRepository
                    .GetAll()
                    .WhereIf(
                        !input.Filter.IsNullOrEmpty(),
                        p => p.Name.Contains(input.Filter)
                    )
                    .WhereIf(
                        input.Id != null,
                        p => p.Id == input.Id
                    )
                     .WhereIf(
                          !input.TagSearch.IsNullOrEmpty(),
                         p => p.TagList != null && p.TagList.Any(t => input.TagSearch.Split(";", StringSplitOptions.None).Contains(t)));
    

    and input DTO is :

        public class GetAddressesInput : PagedAndSortedInputDto, IShouldNormalize
        {
            public string Filter { get; set; }
    
            public long? Id { get; set; }
    
            public string TagSearch { get; set; }
    
            public void Normalize()
            {
                if (Sorting.IsNullOrWhiteSpace())
                {
                    Sorting = "CreationTime DESC";
                }
            }
        }
    
  • User Avatar
    0
    maliming created
    Support Team

    hi

    I think EF Core can't translate these codes, you can consider changing to other methods.

    input.TagSearch.Split(";", StringSplitOptions.None)

    .WhereIf(!input.TagSearch.IsNullOrEmpty(),
    		p => p.TagList != null && p.TagList.Any(t => input.TagSearch.Split(";", StringSplitOptions.None).Contains(t)));
    
  • User Avatar
    0
    Ricavir created

    Hi,

    Yes I've tried to replace split method by creating a list<string> before query :

    List<string> tagSearch = new List<string>();
    if (!String.IsNullOrEmpty(input.Tags))
        tagSearch = input.Tags.Split(";", StringSplitOptions.None).ToList();    
    

    then

    .WhereIf( !input.Tags.IsNullOrEmpty(),
                         p => p.TagList != null && p.TagList.Any(t => tagSearch.Contains(t)));
    

    but this is not the problem. From my understanding of EF Core 3.x, I need to split in several queries to avoid performance issues.... but I don't see how to do it.

    I've tried to convert to SQL query but didn't get to it also...

  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi @ricavir,

    Could you create a demo project and reproduce this on a new project ? If you can do that, we can try to help you using that project.

    Thanks,

  • User Avatar
    0
    Ricavir created

    Hi @ismcagdas,

    Tks. I send to you the demo projet and a video to [email protected]. Let me know if you need anything else.

  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi @ricavir

    Thanks, I'm downloading the project and get back to you if we can or cann't find a solution.

  • User Avatar
    0
    Ricavir created

    Great, tks @ismcagdas !

  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi @ricavir,

    For filtering TagList property, I have implemented such approach;

    public async Task<PagedResultDto<GetAddressForViewDto>> GetAll(GetAllAddressesInput input)
    {
    	var filteredAddresses = _addressRepository.GetAll()
    				.WhereIf(!string.IsNullOrWhiteSpace(input.Filter), e => false || e.Name.Contains(input.Filter) || e.City.Contains(input.Filter))
    				.WhereIf(!string.IsNullOrWhiteSpace(input.NameFilter), e => e.Name == input.NameFilter)
    				.WhereIf(!string.IsNullOrWhiteSpace(input.CityFilter), e => e.City == input.CityFilter);
    
    	if (!input.Tags.IsNullOrEmpty())
    	{
    		var tagFilter = PredicateBuilder.New<Address>();
    		foreach (var tag in input.Tags.Split(";"))
    		{
    			tagFilter = tagFilter.Or(t => t.TagList.Contains(tag));
    		}
    
    		filteredAddresses = filteredAddresses.Where(tagFilter);
    	}
    
    	var pagedAndFilteredAddresses = filteredAddresses
    		.OrderBy(input.Sorting ?? "id asc")
    		.PageBy(input);
    
    	var addresses = from o in pagedAndFilteredAddresses
    					select new GetAddressForViewDto()
    					{
    						Address = new AddressDto
    						{
    							Name = o.Name,
    							City = o.City,
    							Id = o.Id
    						}
    					};
    
    	var totalCount = await filteredAddresses.CountAsync();
    
    	return new PagedResultDto<GetAddressForViewDto>(
    		totalCount,
    		await addresses.ToListAsync()
    	);
    }
    

    This doesn't throw an exception but as I can see it also doesn't work. It seems like this is a limitation of EF Core. when I change the Tags filter to a static filter like below it also doesn't work even I have 4 in the related database field;

    .WhereIf(!string.IsNullOrWhiteSpace(input.Tags), e => e.TagList.Contains("4"))

    Here are the issues I have found on EF Core GitHub repo; https://github.com/dotnet/efcore/issues/11156 https://github.com/dotnet/efcore/issues/10434

    You may want to use another approach than using ValueConverter considering its limitations; https://docs.microsoft.com/en-us/ef/core/modeling/value-conversions#limitations

  • User Avatar
    0
    Ricavir created

    Hi @ismcagdas,

    Thanks for your try. I don't use value converters so much but it is very useful sometimes. As it is a production app, I wanted to avoid creating new tables and manage data migrations...

    I found a workarround by querying directly in SQL.

    I used following code :

    private IQueryable<Address> GetIQueryable(GetAllAddressesInput input)
    {
    	//Due to EF CORE 3.X query limitations with value converters, tags must be queried directly in SQL 
    	//TODO : revert to normal LINQ query when EF CORE 3.X limitations will be fixed
    	if (!input.Tags.IsNullOrEmpty())
    	{
    		string tagSearch = input.Tags;
    		if (!tagSearch.Contains(";"))
    		{
    			return _dbContextProvider.GetDbContext(Abp.MultiTenancy.MultiTenancySides.Tenant).Addresses.FromSqlRaw(
    				"SELECT * FROM dbo.LsAddresses WHERE TenantId = {0} and {1} IN(SELECT value FROM STRING_SPLIT(TagList, ';'))", AbpSession.TenantId, tagSearch
    				);
    		}
    		else
    		{
    			string query = String.Format("SELECT * FROM dbo.LsAddresses WHERE TenantId = {0}", AbpSession.TenantId);
    			foreach(var tagSplited in tagSearch.Split(";"))
    			{
    				query = query + String.Format(" and {0} IN(SELECT value FROM STRING_SPLIT(TagList, ';'))", tagSplited);
    			}
    			return _dbContextProvider.GetDbContext(Abp.MultiTenancy.MultiTenancySides.Tenant).Addresses.FromSqlRaw(query);                
    		}                    
    	}
    	else
    		return _addressRepository.GetAll();
    }
    
    
    public async Task<PagedResultDto<GetAddressForViewDto>> GetAll(GetAllAddressesInput input)
    {
    	var filteredAddresses = GetIQueryable(input)
    				.WhereIf(!string.IsNullOrWhiteSpace(input.Filter), e => false || e.Name.Contains(input.Filter) || e.City.Contains(input.Filter))
    				.WhereIf(!string.IsNullOrWhiteSpace(input.NameFilter), e => e.Name == input.NameFilter)
    				.WhereIf(!string.IsNullOrWhiteSpace(input.CityFilter), e => e.City == input.CityFilter);
    
    
    	var pagedAndFilteredAddresses = filteredAddresses
    		.OrderBy(input.Sorting ?? "id asc")
    		.PageBy(input);
    
    	var addresses = from o in pagedAndFilteredAddresses
    					select new GetAddressForViewDto()
    					{
    						Address = new AddressDto
    						{
    							Name = o.Name,
    							City = o.City,
    							Id = o.Id
    						}
    					};
    
    	var totalCount = await filteredAddresses.CountAsync();
    
    	return new PagedResultDto<GetAddressForViewDto>(
    		totalCount,
    		await addresses.ToListAsync()
    	);
    }
    

    You need to build an IQueryable first to search from tags, then you can continue with classic EF LINQ query.

    Hope this can help others.

  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi @ricavir

    Thank you for sharing your final solutin :).