Filtering on projected property field in LINQ to entities query

Question

The following LINQ to Entities query, a sub-query is performed and its results projected to MyViewModel.

I am looking to obtain all ProjectedModel objects with the myText string variable in the Text property of the SubModel.

var items = (from t1 in db.MyTable
                select new MyModel
                {
                    Id = t1.MyId,
                    SomeItems = (from s1 in db.MyOtherTable
                        where s1.LinkedId == t1.Id
                        select new SubModel
                        {
                            Id = s1.Id,
                            Text = s1.Text
                        })
                }).ToList();

Pseudo code would look like:

string myText = "abc";
items = items where SomeItems.Text contains myText

score 1 · Accepted Answer · answered Mar 27 '14 at 17:38

1

If you want to get all of the items where Any of the sub items have the given text, then that's easy enough to write:

items = items.Where(item => 
    item.SomeItems.Any(subItem => subItem.Text.Contains(myText)));

If you want All of the items to match, then use All. As it is, your requirements are currently incomplete as they assume there is only one sub-item. (If you know there will always be exactly one sub-item then don't make SubItems a collection, make it a single item, and get the first item in the query.)

answered Mar 27 '14 at 17:38

Servy

202,030
26
332
449

Tests on this resulted in "This method is not supported against a materialized query result." – Nick Mar 27 '14 at 17:59
Just .ToList()'ed SomeItems. Sorted. Thanks. – Nick Mar 27 '14 at 18:07

score 0 · Answer 2 · edited May 23 '17 at 12:28

0

you can do it like this way

Updated code

item.Where(w => myText.Contains(w.Text));

or you can use .StartsWith() or .EndsWith()

reference : LIKE

My answer is outdated or it may not help you but some more things which may help you

var selectItedm = (from a in item   
                  where SqlMethods.Like(a.Text,"%"+myText+"%")  
                  select new {a.Id,a.Text}).ToList();

you can use many more SqlMethods just include System.Data.Linq.SqlClient; reference

edited May 23 '17 at 12:28

Community

1
1

answered Mar 27 '14 at 17:32

Amit Bisht

4,870
14
54
83

Thanks for the response. I have used contains but am looking to use it on the nested object's property. – Nick Mar 27 '14 at 17:34
I'm not looking to filter on Id, I'm looking to filter on the Text property within the SubModel. – Nick Mar 27 '14 at 17:38

Filtering on projected property field in LINQ to entities query

2 Answers2

Updated code