1

Hi I am using Predicate builder to build up my where clause. I noticed that it was doing a case sensitive comparison against the entity datasource. Some quick googling implies this is a feature of linqtoentities. so i have had to modify my code to do

whereClause = whereClause.And(x => x.county.Equals(oSearch.County, StringComparison.OrdinalIgnoreCase ));

rather than previously doing

whereClause = whereClause.And(x => x.county == oSearch.County);

The problem is now it appears to be failing building a whereclause.

incidentally i am using the code below which was working before.

var tbl = db.tbl_members.AsExpandable().Where(whereClause.Compile());

I have code which does a foreach (var item in Model) this is now failing with a "Object reference not set to an instance of an object" if i examine the model using quick view it now shows this

tblMembers = {System.Linq.Enumerable.WhereEnumerableIterator<tbl_members>}

rather than

  tblMembers = {SELECT [Extent1].[id] AS [id], 
[Extent1].[membership_id] AS [membership_id], 
[Extent1].[membership_type] AS [membership_type], 
[Extent1].[institution] AS [institution], 
[Extent1].[full_name] AS [full_name], 
[Extent1].[address1] AS [address1], 
...

which it showed previously regardless of any results or not.

Tim
  • 7,401
  • 13
  • 61
  • 102
  • It appears to be? What actually *is* happening? – Jon Skeet Feb 13 '12 at 10:28
  • well the previous working code throws a null exception and when iexamine the tbl object there is no data returned. – Tim Feb 13 '12 at 10:34
  • *Where* does it throw the exception? Please give us all the information we might need... see http://tinyurl.com/so-hints – Jon Skeet Feb 13 '12 at 10:36
  • ok examining the result in debug mode it appears to be returning me a different type of result.Previously regardless of if i had any results or not i could do a foreach (var item in Model) but now it throws a object variable not set to an instance of an object and the object in quickview shows base {System.Linq.Enumerable.Iterator} = {System.Linq.Enumerable.WhereEnumerableIterator} rather than Model = {SELECT [Extent1].[id] AS [id], [Extent1].[membership_id] AS [membership_id], ... – Tim Feb 13 '12 at 10:56
  • And again, you're not really giving us all the information you have. Please read that link and edit your question. – Jon Skeet Feb 13 '12 at 10:57
  • sorry jon was trying to insert the comment and CR saved my answer before i had finished. – Tim Feb 13 '12 at 11:03
  • It's more useful to edit the question than to put it into comments. – Jon Skeet Feb 13 '12 at 11:10

2 Answers2

1

The problem is that linq has translation for 

whereClause = whereClause.And(x =>
x.county.Equals(oSearch.County,StringComparison.OrdinalIgnoreCase )

in the sql. You can try:

whereClause = whereClause.And(x => x.county.ToLower()==oSearch.County.ToLower());
Arion
  • 31,011
  • 10
  • 70
  • 88
  • this syntax works if i just use it against the where, its predicatebuilder it appears which doesnt like it. – Tim Feb 13 '12 at 10:38
  • incidentally the workaround isnt really a valid solution as it will cause the resulting sql not to use indexes. – Tim Feb 13 '12 at 10:39
0

Given this:

tblMembers = {System.Linq.Enumerable.WhereEnumerableIterator<tbl_members>}

it looks like you're inadvertently using LINQ to Objects. Check whether your copy of PredicateBuilder is actually in terms of expression trees (Expression<Func<T, bool>>) or delegates (Func<T, bool>) - you should be using expression trees.

EDIT: As noted in comments, this is the problem:

var tbl = db.tbl_members.AsExpandable().Where(whereClause.Compile());

That's compiling the expression tree into a delegate, and then calling Enumerable.Where. Without the Compile() call:

var tbl = db.tbl_members.AsExpandable().Where(whereClause);

... we end up calling Queryable.Where using the expression tree, which can then be converted into SQL.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • Im using the LinqKit.dll and instantiating my query as var whereClause = LinqKit.PredicateBuilder.True(); sorry im not sure how to check if its using expression trees – Tim Feb 13 '12 at 11:35
  • System.Linq.Expressions.Expression> = System.Linq.Expressions.Expression> – Tim Feb 13 '12 at 11:47
  • @Tim: Okay, that sounds like it *is* using expression trees... what is `AsExpandable()`? – Jon Skeet Feb 13 '12 at 12:04
  • http://www.albahari.com/nutshell/predicatebuilder.aspx in this document he seems to say this is necessary for linq to entity. – Tim Feb 13 '12 at 13:09
  • @Tim: You've shown what the debugger shows for `tblMembers`, but you haven't told us where that's coming from... could you give more information please? – Jon Skeet Feb 13 '12 at 13:11
  • tblMembers = db.tbl_members.AsExpandable().Where(whereClause); just modified my above to remove the compile call. I had put this and the asexpandable in due to the suggestions from the help http://www.albahari.com/nutshell/predicatebuilder.aspx it appears to work now. Wierd? Thanks for your help Jon! – Tim Feb 13 '12 at 13:39
  • @Tim: Ah, I hadn't seen that you were calling `Compile()`. Yes, that makes the whole thing make sense - that was converting from an expression tree to a delegate. – Jon Skeet Feb 13 '12 at 13:42
  • yes thanks jon still a bit confused as the help says to do this. but it works so i am happy :) – Tim Feb 13 '12 at 13:49
  • @Tim: "the help" - which help? There are definitely places where it's good to call `Compile()`, but this isn't one of them... – Jon Skeet Feb 13 '12 at 13:56
  • just found where i saw it http://www.albahari.com/nutshell/linqkit.aspx under "Plugging Expressions into EntitySets / EntityCollections: The Solution" – Tim Feb 13 '12 at 14:03
  • @Tim: But that's *within* a where clause in a query expression. It's not really the same thing. It feels like it would probably be worth you studying a bit more about how LINQ works, and the difference between delegates and expression trees. You may find http://msmvps.com/blogs/jon_skeet/archive/2011/02/20/reimplementing-linq-to-objects-part-43-out-of-process-queries-with-iqueryable.aspx useful. – Jon Skeet Feb 13 '12 at 14:07
  • thanks jon for your patience. i will read and inwardly digest. my linq knowledge is pretty basic, much more familiar with raw tsql – Tim Feb 13 '12 at 14:17