Monday, March 21, 2011

EF4 + ESQL for not supported options

So say you want to support wildcard searches against a SQL db. Yes this is probably horrible performance wise, but... useful or as an example how to do ESQL against an EF4 context.

Linqpad against my EF4 context:

  internal IEnumerable<IPlayerFull> SearchForPlayer(string playerNameLike, byte? universeId)

var q= from p in q.Players.Where(p => p.UniverseId == universeId) 
.Where("it.Name like @Name"new ObjectParameter[] { new ObjectParameter("Name", playerNameLike) })
.Where(p => p.UniverseId == universeId)
       orderby p.Name
      select p;
 return q;
bizarre eh? EF4 note, not related to ESQL:

Also note it does not automatically deal with null properly... for instance if the query was supposed to match exactly with the value in the universeId and that value was null... to get the proper handling you'd have to write out...

.Where(p=>p.UniverseId==universeId || (universeId.HasValue==false && p.UniverseId==null) )
.Where(p=>p.UniverseId==universeId || (universeId==null && p.UniverseId==null) )
but what I actually needed for this situation was more like: 
 .Where(p => p.UniverseId == universeId || (universeId == 1 && p.UniverseId == null))
because... the data and code were never properly and cleanly converted in the event of a legacy app connecting and trying to query. universeId should not be nullable on either side.

No comments:

Post a Comment