CODEWITHSUNDEEP
.netlinqentity-framework
Scott Mayfield
Scott Mayfield

Reputation: 2274

Using Linq 2 EF, how would I find a guid starting with 'x'?

Using Linq2Entities (EF4), I need to query an entity that has a Guid property (call it id). I need to query whether or not my entity has any records that have ids starting with a given prefix.

In essence, I need something like 

from items in myEntity 
where items.id.ToString().StartsWith(prefix)
// rest of the query

Now, I know that L2EF doesn't support conversions of Guid members using ToString().

The SqlFunctions.StringConvert() helper doesn't support it either as it doesn't accept a Guid argument.

And I can't use SQL's LIKE, as in 

from items in myEntity 
where items.id like 'prefix%'

because it's also not supported by L2EF.

All of the recommendations I've found so far suggest using Contains(), but that's simply not the same as starts with.... In the case of Guids, for example, the first 8 chars might be found in the last 13 characters.

So, how would YOU use L2EF to query records that have a Guid starting with "prefix" ?

I can think of some hacks like converting the uniqueidentifier field on the back end SQL database to a varchar(), but I'd really like to understand if I'm just doing it wrong before resorting to something like that.

Upvotes: 4

Views: 595

Answers (2)

bkaid
bkaid

Reputation: 52073

Use ExecuteStoreQuery on your context and execute a custom SQL statement directly against your context. Something like:

string prefix = '00000000';       
const string sql = "select * from myEntities where id like @prefix + '%'"; 
var matches = context.ExecuteStoreQuery<MyEntityType>(sql, prefix);

Also, check out this MSDN example.

Upvotes: 1

Ryan Bennett
Ryan Bennett

Reputation: 3432

Two options: 1) change your entity data model so that whatever "x" signifies, it is moved to a property on that entity instead of tightly coupled to the unique id

2) get the whole list of entities into a collection, then after they are loaded into memory, go through and do a id.ToString().Contains() query on the collection. Once it has been loaded into memory the CLR will allow this. Though, horrible overhead.

I'd go with #1 if possible.

Upvotes: 4

Related Questions