CODEWITHSUNDEEP
c#sqllinqlambdaruntime-error
user2015534
user2015534

Reputation: 89

Error while using Where clause in LINQ to SQL?

i am getting below error while applying filter condition to the LINQ to SQL table. Is there a way to create the SQL query with Where condition in LINQ to SQL?

Error: "Method 'Boolean Equals(System.String, System.String, System.StringComparison)' has no supported translation to SQL."

Below is the code

  public IQueryable<DocumentReplacementPack> GetDocumentReplacementPack(string state,int typeID)
    {
        if (this.DataContext.DocumentReplacementPacks.Count() > 0)
            **return this.DataContext.DocumentReplacementPacks.Where(d => string.Equals(d.State, state, StringComparison.InvariantCultureIgnoreCase));**
        else
            return this.DataContext.DocumentReplacementPacks;
    }

Upvotes: 0

Views: 570

Answers (2)

dbarnes
dbarnes

Reputation: 1833

No this is not an error, sadly this comes up a lot more than you think. Let's look at how linq converts expressions to sql from a high level

this.DataContext.DocumentReplacementPacks.Where(d => string.Equals(d.State, state, StringComparison.InvariantCultureIgnoreCase));

this will generate a sql call that looks similar to 

select * from [DocumentReplacementPacks] where ??

now what is going to happen is the expression is going to get to the string.Equals call and since there is no REAL equivalent of string.Equals in sql, linq has no idea how to generate the sql string. Since string.Equals is a c# function and not a sql function. A really good example would be something like

public bool AreEqual(string one, string two){
     return one.ToCharArray()[0] == two.ToCharArray()[0];
}

Now this is a very trivial example, but think if you had a linq expression like such:

return this.DataContext.DocumentReplacementPacks.Where(d => AreEqual(d.State, state));

How would you expect this to get translated? There is just no way, how would linq know exactly how to the function acts.

The way around this would be to switch to == since linq knows how to translate that.

return this.DataContext.DocumentReplacementPacks.Where(d =>d.State == state);

and let the database do the InvariantCultureIgnoreCase, now if that is not an option you will just have to read the data into memory and do the call.

this.DataContext.DocumentReplacementPacks.ToList().Where(d => string.Equals(d.State, state, StringComparison.InvariantCultureIgnoreCase));

but I do not advise that since reading a whole dataset into memory can be very very expensive.

* OFF TOPIC *

Also I was looking at your code and I highly recomment changing this.DataContext.DocumentReplacementPacks.Count() > 0) to this.DataContext.DocumentReplacementPacks.Any())

Explanation. I'm often disappointed with how people use linq without truly understanding IEnumerable/IQuerable but how else do you become a better developer?

Upvotes: 1

Eckd
Eckd

Reputation: 358

MS SQL by default treats strings case insensitive, so you should use '==' which can be translated by LINQ2SQL

Upvotes: 0

Related Questions