none
How to build dynamic expression tree with many to many relation in entity framework RRS feed

  • Question

  • Hi, I want to generate a dynamic Expression tree. I have a relation database and I am using Entity Framework.

    I have a structure With the table Table and a many to many relation to the table Relation - With a table in between called TableRelation :)

    How can I build this Query:

    _db.Table.Where(p => p.Relation.Select(o => o.Id).Contains(3))?

    In sql this would look like

    Select *
    from Table
    where Table.Id in
    (Select TableRelation.Table_Id from TableRelation where Table_Id = Table.Id and RelationId = 3)

    I hope this makes sence.


    • Edited by Vagsnes Monday, April 4, 2016 5:57 AM
    Monday, April 4, 2016 5:56 AM

Answers

  • Hi Vagsnes,

    You could use Expression.Lambda method generate lamada Expression

    var table = Expression.Parameter(typeof(Area), "parent"); var expressionParam = Expression.Parameter(typeof(Department), "child"); var childColumn = Expression.PropertyOrField(expressionParam, "Id"); var childWhere = Expression.Equal(childColumn, Expression.Constant(3, typeof(int))); var lambda = Expression.Lambda(childWhere, expressionParam);

    var result = queryableData.Where(lambda);


    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 27, 2016 9:11 AM
    Moderator

All replies

  • Hi Vagsnes,

    According to your description, you need a sub-query, and I create similar demo for your reference.

    using (var db = new TestEntities1())
                {
                  var query = db.Posts.Where(p => db.Blogs.Where(t => t.Id == 1 && p.Blog_Id == t.Id).Select(t => t.Id).Contains(p.Blog_Id));
    
                    foreach (var item in query)
                    {
                        Console.WriteLine("{0}--{1}", item.Blog_Id, item.Title);
                    }
                    Console.ReadKey();
                }

    I hope it's helpful to you.

    Best regards,

    Cole Wu

    Tuesday, April 5, 2016 4:48 AM
    Moderator
  • Hi, I know how to Write the linq Expression. I need help to build the Expression tree. I want to build a dynamic filter.

    I have tables called Prospect, ProspectOwner and Owner. ProspectOwner is a "many to many" relation between Prospect and Owner.

    How can I build an Expression tree to get all Prospects With OwnerId = 3

    var table = Expression.Parameter(typeof(Prospect), "parent");
    var expressionParam = Expression.Parameter(typeof(Owner), "child");
    var childColumn = ExpressionPropertyOrField(expressionParam, "OwnerId");
    var childWhere = Expression.Equal(childColumn, Expression.Constant(3, typeof(int)));
    Now I've got the subselect - but how to add this the main Query.. I just don't get it...

    Wednesday, April 6, 2016 9:44 AM
  • Hi Vagsnes,

    You could use Expression.Lambda method generate lamada Expression

    var table = Expression.Parameter(typeof(Area), "parent"); var expressionParam = Expression.Parameter(typeof(Department), "child"); var childColumn = Expression.PropertyOrField(expressionParam, "Id"); var childWhere = Expression.Equal(childColumn, Expression.Constant(3, typeof(int))); var lambda = Expression.Lambda(childWhere, expressionParam);

    var result = queryableData.Where(lambda);


    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 27, 2016 9:11 AM
    Moderator