Converting string to another type in both LINQ to Entities and Objects (question)

[jbhelm]

First, great library, very useful!

We have a scenario where we need the same dynamic LINQ string to execute both against a database using LINQ to Entities, and in-memory objects using LINQ to Objects. Normally this works fine, however, we also occasionally need to convert string values to some other type (integer for example). The problem is the way you do this when using LINQ to Entities is by using Cast(), but using LINQ to Objects you must use the appropriate Convert function, and each call is not supported by the other.

For example, take the following dynamic LINQ:

Data.Where(Example == 1).Select(Value).Cast("int").FirstOrDefault()

This works as expected for a database because a Cast in a database is actually a conversion. Executing this same thing against in-memory objects of course results in the following error because a Cast in .NET is not a conversion, just a cast:

Unable to cast object of type 'System.String' to type 'System.Int32'.

For LINQ to Objects, the Cast() would need to be replaced by something like:

Data.Where(Example == 1).Select(Value).Select(Convert.ToInt32(it)).FirstOrDefault()

...but that doesn't work with LINQ to Entities because it doesn't support the Convert functions.

Is there something obvious I'm missing to support parsing/conversions in both scenarios with the same syntax?

I thought I might use the DynamicLinqTypeAttribute and make a new IQueryable extension method like Convert(type) that looks at the queryable and determines if it's running against in-memory objects or a database and add the appropriate conversion to the queryable, but it seems extension methods are not supported for LINQ to Entities. (A side question is would it be possible to support IQueryable extension methods that also return IQueryable for LINQ to Entities?)

If there's no built-in way to do this, is there some other way to extend the IQueryable functions the library supports? The document mentions "There are several ways to extend the functionality from Dynamic LINQ library" on the "Extending" page, but then only mentions the DynamicLinqTypeAttribute and no other methods of extending the library.

Any help or suggestions are appreciated. Thanks!

[zspitz]

This is not an issue specific to Dynamic LINQ. I believe the following query would fall on in-memory objects:

IQueryable<string> qry = new List<int>().AsQueryable().Cast<string>();

even though the corresponding query would succeed against EF:

IQueryable<int> baseQuery = /* populate here */;
IQueryable<string> qry = baseQuery.Cast<string>();

As I understand it, EF handles this using a set of built-in conversions.

But in similar scenarios, I would use the in-memory variant as the baseline. I would use a visitor to rewrite the expression tree for EF -- in this case, replacing calls to Convert.ToInt32 in the outermost node of a Select expression, with a call to Cast.

I would then call qry.Provider.CreateQuery (CreateQuery) passing in the rewritten expression tree.

This could be in its own extension method off of IQueryable. Also, it could have some logic to test whether the provider is the in-memory provider, or the EF provider; and only rewrite the query if needed.

I wrote sample code for an article that does something similar, rewriting the VB Like operator and its pattern into calls to DbFunctions.Like and SQL Server's LIKE pattern syntax link.

[StefH]

Hello @jbhelm,

I've added some code to use e.g. "int.Parse" for convert a string to an int.

PR is here.
#686

Note that this will only work when using Linq2Objects.

[StefH]

Hello @jbhelm,

Did you have time to verify the PR?

[jbhelm]

Hi @StefH

Apologies for the delay. I did try it and can confirm it does work for LinqToObjects.

I will say that for us, this is no longer necessary. Since I originally posted this, we have begun migrating our project from EF6 to EF Core. EF Core LinqToEntities now properly supports Convert.ToXXX(), and actually they do not support conversion using Cast() anymore the way EF6 did (see dotnet/efcore#28282). So, EF Core LinqToEntities actually works the way System.Linq.Dynamic.Core works before this PR.

[StefH]

Thanks for the update.

Closing this issue now.