Geek Answers Handbook

Using Linq 2 EF, how do I find a landmark starting with "x"?

Using Linq2Entities (EF4), I need to request an object that has the Guid property (name it id ). I need to ask if my object has any records for which the id begins with this 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 does not support conversion of Guid members using ToString() .

The SqlFunctions.StringConvert() helper does not support it, since it does not accept the Guid argument.

And I can not use SQL LIKE , as in

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

because it is also not supported by L2EF.

All the recommendations that I have found so far suggest using Contains() , but it's just not the same as starts with .... In the case of Guid s, for example, the first 8 characters can be found in the last 13 characters.

So, how will you use L2EF to request records that have a Guid starting with a "prefix"?

I might think of some hacks, such as converting a uniqueidentifier field to a back-end SQL database in varchar() , but I would really like to understand if I'm just doing it wrong before resorting to something like that.

+4
source share
2 answers

Use ExecuteStoreQuery in your context and execute a custom SQL statement directly against your context. Sort of:

 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 .

+1
source

Two options: 1) change the entity data model so that any value of "x" is transferred to the property on this object instead of tightly associated with a unique identifier

2) get the entire list of entities in the collection, and then after loading them into memory, go through and execute id.ToString (). Contains () the request in the collection. Once it is loaded into memory, the CLR will allow this. Although, terrible overhead.

If possible, I will go with No. 1.

+4
source

More articles:


All Articles