Using Join with IQueryable LINQ

Question

I am trying to implement Inner join query on two tables opportunityProducts and Products where I am supposed to return Iqueryable element in my MVC web API service. But from below, I am not able to get result as it throws error for conversion.

public IQueryable<OpportunityProducts> GetProductsByShipID(int id)
 {
   IQueryable<OpportunityProducts> oppProductss = 
                  from c in db.OpportunityProducts
                  from p in db.Products
                  where p.ProductID == c.ProductID
                  select new { c.Quantity,c.ProductDesc,c.RemainingQuantity, p.QtyInHand};
    return oppProductss;
  }

your variable is `IQueryable` but you returnin anonymouse object with this line `new { c.Quantity,c.ProductDesc,c.RemainingQuantity, p.QtyInHand}` can you say what are you really trying to do? — teo van kot, Sep 07 '15 at 07:41
You should not return an anonymous type. You either have to select the `c` variable which is a `OpportunityProducts` or return a new type which also contains properties of both. — Tim Schmelter, Sep 07 '15 at 07:46

Rahul Singh · Accepted Answer · 2015-09-07T07:48:38.427

1

You need to fill the Type you wish to return instead of returning an anonymous type. Here since you are querying the OpportunityProducts, I think you don't have QtyInHand property. So you can either return a new type altogether or add this property.:-

IQueryable<ResultantProducts> oppProductss = 
                  from c in db.OpportunityProducts
                  from p in db.Products
                  where p.ProductID == c.ProductID
                  select new ResultantProducts
                          { 
                               Quantity = c.Quantity,
                               ProductDesc = c.ProductDesc,
                               RemainingQuantity = c.RemainingQuantity, 
                               QtyInHand = p.QtyInHand
                          };

edited Sep 07 '15 at 07:48

answered Sep 07 '15 at 07:46

Rahul Singh

21,585
6
41
56

Why do you assume that `OpportunityProducts` has also a property `QtyInHand`? – Tim Schmelter Sep 07 '15 at 07:48
1

@TimSchmelter - Yup, you are right! Corrected. Thanks. – Rahul Singh Sep 07 '15 at 07:50
I do have one problem while executing my query "The entity cannot be constructed in a LINQ to Entities query". – Mohammed Faisal Khan Sep 07 '15 at 08:42
@user1554374 - You will have to pass it as list, check this link: http://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query – Rahul Singh Sep 07 '15 at 08:49
Except it returns an IEnumerable – Tamas Ionut Nov 18 '16 at 14:16

score 0 · Answer 2 · answered Sep 07 '15 at 07:47

I see an error in your code. You should return objects of type OpportunityProducts, I mean:

    public IQueryable<OpportunityProducts> GetProductsByShipID(int id)
    {
       IQueryable<OpportunityProducts> oppProductss = from c in db.OpportunityProducts
              from p in db.Products
              where p.ProductID == c.ProductID
              select new OpportunityProducts    // <---- THIS!
              { 
                    Quantity = c.Quantity,
                    ProductDesc = c.ProductDesc,
                    RemainingQuantity = c.RemainingQuantity, 
                    QtyInHand = p.QtyInHand
              };
       return oppProductss;
    }

I hope it helps you.

Regards,

Julio

Same question here: Why do you assume that `OpportunityProducts` has also a property `QtyInHand`? — Tim Schmelter, Sep 07 '15 at 07:48

score 0 · Answer 3 · answered Sep 07 '15 at 08:49

I think you can create class called ResultProducts with all properties(same data type in the original table (nullable also need to put)) what you want to get. Then you can return that object.

public class ResultProducts 
    {

            public int Quantity { get; set; }
            public string ProductDesc { get; set; }
            public int  RemainingQuantity { get; set; }
            public int QtyInHand { get; set; }
     }

public IQueryable<ResultProducts> GetProductsByShipID(int id)
        {
            var oppProductss =from c in db.OpportunityProducts
                              from p in db.Products
                              where p.ProductID == c.ProductID
                              select new ResultProducts()
                              { 
                               Quantity =c.Quantity,
                               ProductDesc= c.ProductDesc,
                               RemainingQuantity=c.RemainingQuantity,
                               QtyInHand=p.QtyInHand 
                               };

            return oppProductss ;
        }

I hope this will work.

Using Join with IQueryable LINQ

3 Answers3