The LINQ expression could not be translated System.Version.Parse

Question

I am using EF and I'm trying to query some records which have a version column like this:

var servicesWhichExistInDifferentDataCenters = dbContext.CurrentRunningServices
    .Where(r => r.DeployedRing == ring 
        && r.DataCenter == defaultDataCenterIndex 
        && r.IsActive)
    .AsEnumerable()
    .Where(r => dbContext.CurrentRunningServices
        .Any(r2 => r2.DeployedRing == r.DeployedRing 
          && r2.DockerImageName == r.DockerImageName 
          && r2.DataCenter != r.DataCenter 
          && Version.Parse(r2.DeploymentVersion) != Version.Parse(r.DeploymentVersion)))
    .AsEnumerable()
    .ToList();

The LINQ expression 'DbSet() .Any(c => c.DeployedRing == __r_DeployedRing_0 && c.DockerImageName == __r_DockerImageName_1 && c.DataCenter != __r_DataCenter_2 && Version.Parse(c.DeploymentVersion) != __Parse_3)' could not be translated. Additional information: Translation of method 'System.Version.Parse' 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.

The query runs fine locally in VS, but I get the above error when running it on the server, even though the server is just running an image prepared from the local code. Why is it happening?

Answer 1

Version.Parse(r2.DeploymentVersion) != Version.Parse(r.DeploymentVersion)

This comparison cannot be "translated" to SQL and run on the server.

You can either insert another .AsEnumerable() here:

dbContext.CurrentRunningServices.AsEnumerable().Any

which would drag all the data and processing over to the client.

OR

You could just skip the version parsing and compare the strings on the database server, which would enable you to remove all your .AsEnumerable()s, that I guess are only in there to debug/guess a solution to your problem.

If your version field is not normalized, then normalize it before putting it into the database, not on each comparison again and again.

---

As for why this is not happening if you run it locally, I can only offer an educated guess: maybe you are not running a real SQL server locally, but an in-memory database of sorts? Many of those use shortcuts and do not actually translate your statement to SQL.