locked
Entity Framework and IN operator: justified dissappointment? RRS feed

  • Question

  • I'm just finishing a project for my graduation. It's goal is to measure the performance of three ORM systems: LINQ to SQL, Entity Framework and NHibernate in a semi-real application. The idea is to take the TPC-E test (http://www.tpc.org) and implement the transactions using those three ORM tools.

     

    During development I found that I cannot figure out how to use the IN set operator in a clean way. The code below explains the problem:


    LINQ fails:

    Code Snippet

    List<Employees> result = null;

     

    NorthwindEntities ent = new NorthwindEntities();

    List<int> intIds = Enumerable.Range(1, 7).ToList();

     

    var qLinq = from emp in ent.Employees

      where intIds.Contains(emp.EmployeeID)

      select emp;

    try

    {

    result = qLinq.ToList();

    }

    catch(NotSupportedException exc)

    {

    //Message: LINQ to Entities does not recognize the method 'Boolean Contains(Int32)' method, and this method cannot be translated into a store expression.

    }

     

    Entity SQL try #1:

    Code Snippet

    try

    {

    var qESql1 = ent.CreateQuery<Employees>(

    "select value e from Employees as e"

    + " where e.EmployeeID in @idList", //same results with {@idList}

    new System.Data.Objects.ObjectParameter("idList", intIds));

    }

    catch (System.ArgumentOutOfRangeException exc)

    {

    //Message: The specified parameter type 'System.Collections.Generic.List`1[[System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]' is not valid. Only scalar types, such as System.Int32, System.Decimal, System.DateTime, and System.Guid, are supported.

    }

     

     

    Entity SQL try #2:

    Code Snippet

    List<string> strIds = intIds.Select(id => id.ToString()).ToList();

    string strIdConcat = strIds.Aggregate(strIds[0], (agg, id) => agg + ", " + id);

    var qESql2 = ent.CreateQuery<Employees>(

    "select value e from Employees as e"

    + " where e.EmployeeID in @idList", //same results with {@idList}

    new System.Data.Objects.ObjectParameter("idList", strIdConcat));

    try

    {

    result = qESql2.ToList();

    }

    catch (EntitySqlException exc)

    {

    //Message: The right argument of the set expression must be of CollectionType.

    }

     

     

    So is there any other way to get it working instead of concatenating the eSQL string (not using parameters)?

    Will the method IEnumerable.Contains() be eventually supported by LINQ to Entities like it is by LINQ to SQL? It would be best because it would also allow to construct querries with subquerries (when the IEnumerable used is a different LINQ to Entites query). It works that way in LINQ to SQL.

     

    Thanks for help,

    Maciek

    Wednesday, May 21, 2008 11:34 AM

Answers

All replies

  • For the LINQ Query, do you try with Any instead of Contains?

    Wednesday, May 21, 2008 12:58 PM
  • LINQ test #2

     

    Code Snippet

    var qLinq2 = from emp in ent.Employees

       where intIds.Any(id => id == emp.EmployeeID)

       select emp;

    try

    {

    result = qLinq2.ToList();

    }

    catch (NotSupportedException exc)

    {

    //Message: Unable to create a constant value of type 'Closure type'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

    }

     

    Unfortunately: not working.

     

    It seems a common problem: fetch entities for which a list of IDs has been provided.

    So how to do it?

     

    Thanks,

    Maciek

    Wednesday, May 21, 2008 3:23 PM
  •  

    Hi Maciek,

    Linq to Entities does not yet support collection-valued parameters (intIds in this example).  For possible workarounds please see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2670710&SiteID=1.

     

    Hope that helps,

    Kati

    Wednesday, May 21, 2008 6:09 PM
  •  

    Thank you, I couldn't find a thread on that topic myself Smile. I still hope that Contains or Any collection methods will be added to  LINQ to Entities before RTM.

     

    Best regards,

    Maciek

    Wednesday, May 21, 2008 7:01 PM
  • I think we pretty much all do. It's so common we shouldn't have to resort to custom expression building to do it ourselves.

     

    -Drew

     

    Thursday, May 22, 2008 2:09 AM