CODEWITHSUNDEEP
c#.netentity-frameworklinq-to-entities
aknuds1
aknuds1

Reputation: 68127

How to search via Entity Framework on objects and their children?

With Entity Framework 4.3 and Linq, I want to match a search string against certain properties of contained objects and also on the properties of child objects. This turns out to be a rather complex query though, and I'm not sure how to do it. For instance, one property is an integer and I'm not allowed to call .ToString() in a Linq query.

In order to give you an idea of what I'm trying to do, consider this example code:

var dbVersions = from ver in db.Versions 
                 where ver.Name.Contains(search) || 
                       ver.Children.Any(c=>c.Id.ToString().Contains(search)) 
                 select ver;

How should I implement this search? Perhaps via a stored procedure?

The database server is SQL Server 2012.

Upvotes: 1

Views: 1536

Answers (2)

Yago Méndez Vidal
Yago Méndez Vidal

Reputation: 884

If you want to use LINQ the framework internally will do the proper optimizations and from my experience the results are quite OK. If you don't want to use an stored procedure and stick to LINQ keeping it all in your class code use:

SqlFunctions.StringConvert((double)c.Id)

for converting your int to an string. Note that there is no overload for int, so you need to cast it to double or decimal.

Upvotes: 5

Jason Evans
Jason Evans

Reputation: 29186

In your situation I would suggest using a stored procedure.

If you end up passing one search term which will be used against multiple columns, then your better off writing a stored proc. I once tried something similar to what you're doing, and the end result was really messy LINQ statements that left me feeling dirty :)

Good reference:

x.ToString() is not supported by the entity framework!

Here's an exmaple of how to use stored procs with EF:

http://blogs.msdn.com/b/bindeshv/archive/2008/11/20/using-stored-procedures-in-entity-framework.aspx

Remember to add the stored proc when you "Update Model from database".

Upvotes: 1

Related Questions