locked
SQL to Ling question, trying to see if a product exists on a pricelist... RRS feed

  • Question

  • I have this SQL statement for checking to see if a product exists on an accounts pricelist:

    SELECT dbo_FilteredAccount.name, dbo_FilteredProduct.name
    FROM dbo_FilteredAccount INNER JOIN (dbo_FilteredPriceLevel INNER JOIN (dbo_FilteredProduct INNER JOIN dbo_FilteredProductPriceLevel ON dbo_FilteredProduct.productid = dbo_FilteredProductPriceLevel.productid) ON dbo_FilteredPriceLevel.pricelevelid = dbo_FilteredProductPriceLevel.pricelevelid) ON dbo_FilteredAccount.defaultpricelevelid = dbo_FilteredPriceLevel.pricelevelid WHERE (((dbo_FilteredAccount.name)="Account A") AND ((dbo_FilteredProduct.name)="Product Z"));

    I am trying to convert it to LINQ but I am struggling with all the inner joins:

                var query = (from acc in svcContexts.AccountSet
                            from pricelevel in svcContexts.PriceLevelSet
                            from prod in svcContexts.ProductSet 
                            from prodpricelevel in svcContexts.ProductPriceLevelSet
                            where acc.Name == "Account A"
                            select prod).ToList();

    I now get the error "A 'SelectMany' operation must be preceeded by a 'Where' operation that filters by an entity ID."  I am lost with this if I am honest, can someone point me in the right direction?

    Matt

    Tuesday, October 13, 2015 1:04 PM

All replies

  • Have a look at this MSDN example on multiple Linq Joins:

    https://msdn.microsoft.com/en-us/library/gg509017.aspx?f=255&MSPPError=-2147217396#DoubleJoin

    Thursday, October 15, 2015 2:37 PM
  • Hi,

    So far I have the following and it works if ran as two separate queries:

    Guid asd = new Guid("64C4E0D9-9D8C-E211-A049-02BF0A86F1E1"); var query1 = (from acc in svcContexts.AccountSet join pl in svcContexts.PriceLevelSet on acc.DefaultPriceLevelId.Id equals pl.PriceLevelId join ppl in svcContexts.ProductPriceLevelSet on pl.PriceLevelId equals ppl.PriceLevelId.Id

                              //join prod in svcContexts.ProductSet on ppl.PriceLevelId.Id equals prod.PriceLevelId.Id   where acc.Name == ac select ppl.ProductId).ToList(); var query2 = query1.Where(p => p.Id == asd).ToList(); if (query2.Count() > 0) { //Product exists on one of the three pricelists. }

    But when I add the join to try and create it as a single query it returns nothing.

    Anyone help with this?

    Matt

    Wednesday, October 21, 2015 9:42 AM
  • Hi,

    I'm no linq-wiz, but aren't you missing a ".Id" in the first join-on-clause?

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Wednesday, October 21, 2015 10:53 AM