VB.Net Linq Question Dynamic query RRS feed

  • Question

  • Hey

    I need a dynamic query with Master/Detail/Detail

    The preparation query is this one:

    Dim locData = From locArticle In myDatacontext.Articles Join
                               locDetails In myDatacontext.Articledetails On
                                             locArticle.Id Equals locDetails.Articleid Join
                               locSellings In myDatacontext.Articlesellings On
                                              locDetails.Id Equals locSellings.Detailid

    The Explanation: One Article can have multiple details and each detail can have multiple sellings.

    What I need now is a Linq expression, which is based on "locData", that brings back all locArticle, which have a count of Detailid > 2 of field locSellings.Detailid

    The table ArticleSellings has a column called "DetailId", which is the FK field for the ArticleDetail table.
    And I need all articles, which have ArticleSellings.DetailId Count >2

    What I need is something like this:

    locData = locData.Where(....)
    Because as I wrote above, this must be a dynamic query!

    Hope you got it ;)

    Here is the SQL query, which should get created

    FROM article
      INNER JOIN articledetails 
        ON (article.ID = articledetails.articleid)
      INNER JOIN articlesellings
        ON ( = articlesellings.detailid)
    GROUP BY article.ID
    HAVING COUNT(articlesellings.detailid) > 2

    THX a lot 

    • Edited by Zero-G. _ Saturday, March 24, 2018 7:13 PM
    Saturday, March 24, 2018 7:12 PM


  • Hey

    Found a solution for my own. I will share it here, so that when somebody needs the same, he/she could get the solution.

    The way is to do it with a second query.

                Dim locSellings = locData.Select(Function(sel) sel.locSellings).GroupBy(Function(grp) grp.Detailid).Where(Function(query) query.Count > 2).Select(Function(sel2) sel2.Key).ToList
                locData = locData.Where(Function(query) locSellings.Contains(query.locSellings.Detailid))

    With this solution it seems to work.

    Have a nice day!

    • Marked as answer by Zero-G. _ Sunday, March 25, 2018 2:19 PM
    Sunday, March 25, 2018 2:19 PM