Search a sub entity using Where(string) ... RRS feed

  • Question

  • Hi to all, 

    I'm trying to do an EF query and filter by a sub entity. In some post, I've found this answer which seems a correct (and easy) answer to this question: 

    (Organisation has many Contacts related in model) 

    from o in Organisation.Include("Contacts")
    from c in o.Contacts
    where o.OrganisationName.StartsWith("Y")
     && c.FirstName.StartsWith("A") && c.LastName.StartsWith("B")
    select o

    Then you can have the Organisations you are looking for, by applying a filter to 
    its sub entity (Contacts). 

    My problem arises as I´m trying to use a "Where(string)" in the query. I've tried some ways to write the query, but can't find the one that is correct. 

    Any ideas would be appreciated

    Thanks and bye Manuel 

    Monday, July 18, 2011 5:25 PM

All replies

  • Hi Manuel;

    Linq to EF does not support a where clause that contains a sting as a parameter that has the filtering options. If you need to do this you will need to use Entity SQL. The following are links to references to the language.

    Entity SQL Overview

    Entity SQL Language


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, July 19, 2011 4:14 AM
  • Hi and thanks for your reply. 

    Oh! yes, you are right, and I use ESQL to do a query that uses a Where clause with a string parameter, as in: 

    var x = from y in ctx.t.Include("....").Where(string parameter) .... .... 

    The above option is a great resource as you can build some criteria in a string parameter and pass to the query to use in the Where clause. 

    What I can't figure out is how to apply a criteria to a sub entity and still use the "Where(string)" in Entity SQL. 

    I tried: 

    ....Where("entityA.entityB.SomeProperty = xyz")  and also 

    ....Where("entityA.entityB.SomeProperty.FirstOrDefault() = xyz") 

    that failed with an error. 

    Some ideas on how to implement above query ... 

    Thanks and bye 

    Tuesday, July 19, 2011 1:02 PM
  • Hi, 

    Just to clarify my initial post: 

    *) if the sub entity is *not* a collection, writing the query, using a string in Where is easy: 

    from c in ctx.Customers.Where("it.Country.ID == 100")   -   (note the use of the string in .Where)

    the query above is perfect because each Customer can have *one* and only *one* Country. 

    *) when the sub entity is a collection, writing the query is a mistery to me: 

    from c in ctx.Customers.Where("it.Invoices.FirstOrDefault().ID == 100") 

    when executing the above query, you get an error saying you should use a sub query ("IN" linq operator?). 

    But, how to mix .Where(string) and subqueries in ONE link query? 

    Thanks and bye ... 


    Wednesday, July 20, 2011 9:56 PM
  • i think you should check this article. http://msdn.microsoft.com/en-us/library/bb738638.aspx if you want to query multiple entities, why not check the following code fragment?

    using (MyContext ctx = new MyContext())
      var customers = from c in ctx.Customers.Include("Orders")
                   select c;



    Just a newbie for everything.
    Thursday, July 21, 2011 7:45 AM
  • Hi Manuel;

    I am just learning ESQL myself and so I am not very strong on its syntax. From my understanding ESQL works much like TSQL and what can go inside its WHERE clause. Here is what I believe should be in the Where.

    ctx.Customers.Where("Exists( SELECT inv.ID FROM it.Invoices AS inv WHERE inv.ID = '100' ) ")

    Good Luck;

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, July 21, 2011 7:59 PM