EF Core 3.x breaking existing EF Core 2.x query #8843 | Support Center

Ricavir created 4 years ago

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)

0

ismcagdas created 4 years ago
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.

Ricavir created 4 years ago

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";
            }
        }
    }

0
maliming created 4 years ago
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)));
0
Ricavir created 4 years ago
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...
0

ismcagdas created 4 years ago
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,
0

Ricavir created 4 years ago

Hi @ismcagdas,

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

ismcagdas created 4 years ago
Support Team

Hi @ricavir

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

Ricavir created 4 years ago

Great, tks @ismcagdas !

ismcagdas created 4 years ago

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

Ricavir created 4 years ago

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.

0

ismcagdas created 4 years ago
Support Team

Hi @ricavir

Thank you for sharing your final solutin :).

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