Using filters for ef core conversion

[hottabych07]

Hi.
Could you help me with the answer to my question.
I have a Value Object called Number

public class Number  : ValueObject
{
   public string Prefix {get;set}
   public DateTime Date {get;set;}
   public int Sequence {get;set;}
}

From this object I form lines such as AFF-01.2022-00001 etc.

In the database I store this value as a string, by using conversion in the ef core.

And it does a great job of filtering that value for the Equal operator. Because your library expands the expression to look like this .Where(p => p.Number == new Number("AFF-01.2022-00001"))

But the problem arises when it comes to the Contains operator. I get an error that you can't call the Contains method on the Number class.

Can you advise me what to do in this situation?

[alirezanet]

Can you use contains method without gridify? I want to find where is the problem exactly.
for example does this query work?

Lst.Where(p => p.Number.Contains(new Number("AFF-01.2022-00001")))

[hottabych07]

Thank you for your answer. No, this query will not work, because my implementation of the Number class does not contain the Contains method.

[alirezanet]

The problem is you didn't implement any logic for Contains method and your expected behavior is ambiguous.
I suggest first, try to implement your logic without gridify using pure LINQ, if it works try to change the hardcoded LINQ query to gridify.

If you explain what are you trying to achieve also share your complete value object (I need to see what operators you've added to the Number class) maybe I could help.
If I understood it correctly you want to use contains method to search for example AFF and get all strings that start with AFF characters right?

[alirezanet]

GridifyMapper could also help,
This is not a complete solution but you can use it as an example (I used record instead of value object for simplicity),

 
var lst = new List<TestEntitiy>();
lst.Add(new TestEntitiy() { Number = "AFF-01.2022-00001" });
lst.Add(new TestEntitiy() { Number = "AFF-01.2022-00002" });
lst.Add(new TestEntitiy() { Number = "AFF-01.2021-00001" });

var gm = new GridifyMapper<TestEntitiy>()
			.AddMap("number", q => q.Number.ToString());			

var test1 = lst.AsQueryable().ApplyFiltering("number=*01.2022", gm);
var test2 = lst.AsQueryable().ApplyFiltering("number=*00002", gm);
var test3 = lst.AsQueryable().ApplyFiltering("number=*AFF", gm);
 
 
Debug.Assert(test1.Count() == 2);
Debug.Assert(test2.Count() == 1);
Debug.Assert(test3.Count() == 3);


public class TestEntitiy
{
	public Number Number {get; set; }
}

public record Number
{
	public Number(string val)
	{
		 
		var pattern = @"^([a-zA-Z]{3})-(\d{2}\.\d{4})-(\d{5})$";
		var match = Regex.Match(val, pattern);
		if (match.Success)
		{
			if (match.Groups[1].Success)
			{
				Prefix = match.Groups[1].Value;
			}

			if (match.Groups[2].Success)
			{
				var sp = match.Groups[2].Value.Split(".");
				Date = new DateTime( int.Parse(sp[1]), int.Parse(sp[0]), 1);
			}
			if (match.Groups[3].Success && int.TryParse(match.Groups[3].Value, out var s))
				Sequence = s;
		}

	}

	public string Prefix { get; init; }
	public DateTime Date { get; init; }
	public int Sequence { get; init; }
  
	public static implicit operator Number(string b) => new Number(b);
	public override string ToString() => $"{Prefix}-{Date.ToString("MM")}.{Date.ToString("yyyy")}-{Sequence.ToString("00000")}"; 
}

[hottabych07]

@alirezanet Hi, your record class is similar to mine. Thanks for the example.

I've done some research and it seems the problem lies in EF Core.

I have this code:

var filterMapper = new GridifyMapper<TestDto>() .AddMap(nameof(Test.Number), w => w.Number.ToString());

var data = _context.Test.Where(w => w.Number.ToString() == "AFF-01.01.2020").ToList(); 

And it fails The LINQ expression 'DbSet() .Where(i => i.Number.ToString() == "AFF-01.01.2020")' could not be translated. Additional information: Translation of method 'object.ToString' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

This is my Conversion code in Ef Core

var converter = new ValueConverter<Number, string>( v => v.ToString(), v => Number.Parse(v));
builder.Property(w => w.Number).HasConversion(converter); 

And as far as I understand, Conversion in Ef core is very restrictive, which makes it impossible to execute an instruction like .Where(w => w.Number.ToString() == "AFF-01.01.2020").

Maybe I'm mistaken.

Maybe I should rethink this. And store Number not through Conversion, but some other way? I'm confused 🤔.
Thanks

[alirezanet]

Hi @hottabych07, I'm on vacation but as soon as I find some free time I'll check this for you. I think we should have an easier way to solve this problem.

[alirezanet]

I found a workaround to solve this problem using LINQ but unfortunately, I couldn't find any solution for gridify yet.
for equal the .Where(q => q.Number == "SomeString") is valid and works fine and for contains you can use EF functions, e.g .Where(q => EF.Functions.Contains(q.Number, "SomeString")).

[alirezanet]

I opened an issue for this (#73 ), I'm not sure how should I handle this situation in gridify when EF doesn't support ToString()! though. feel free to suggest any feature/solution around this.

[hottabych07]

@alirezanet Hi, thanks for your time, I wish you a good rest.

It looks like if you make the Number cast to string, everything works very well and you don't need to use EF.Functions .Where(w => ((string)w.Number).Contains("AFF-01.2022-00001"))

Based on this, I tried doing a mapping

var fm = new GridifyMapper() .AddMap("Number", w => (string) w.Number);

But unfortunately it doesn't work and gives error: TypeConverter cannot convert from System.String.

[alirezanet]

Thank you,
Yeah, I encountered the same error. I think receiving type in the AddMap method instead of the explicit cast could solve this. I'll try to find a solution when I get back (probably next week)

[hottabych07]

@alirezanet I think I have solved this issue.

 var filterMapper = new GridifyMapper<Test>()
            .GenerateMappings()
            .AddMap("Number", w => Convert.ToString(w.Number));

_context.Test.ApplyFiltering("Number=*SomeNumber").ToList();

And it works perfectly through Convert. Thank you for your attention and hint that there is such a beautiful thing as implicit and explicit operators

[alirezanet]

Great! I'm glad that you managed to solve this.