Call a method inside LINQ Query

Question

I have one LINQ Query with custom model. I just wanted to use a method to assign a value for a model property. But When I try to use the custom model it throws some error message like this:

LINQ to Entities does not recognize the method 'System.String GetPONo(Ent, System.String)' method, and this method cannot be translated into a store expression.

Code

var model = (from p in db.PoDetails
             select new porders
             {
                 Category = p.Category,
                 PONO = GetPONo(p, p.Category),
              }).ToList();

Method

public string GetPONo(PoDetail p, string ASD)
{
    if (ASD == "B")
    {
        var PoNo = (from pord in db.Porders where pord.Id == p.PoId select pord.No).FirstOrDefault();
        return PoNo;
    }
    else
    {
        var PoNo = (from porder in db.Porders
                    where porder.Id == (from rec in db.RecommendResources where rec.Id == p.BibId select rec.PoId).FirstOrDefault()
                    select porder.No).FirstOrDefault();
        return PoNo;
    }
}

Answer 1

This is because LINQ to Entities is translated to SQL behind the scenes. Of course there isn't a GetPONo(p, p.Category) method in SQL so your code will fail. You can solve this by calling ToList() to force the query to run before your select.

var model = (from p in db.PoDetails select p).ToList();
model = from m in model
        select new porders
        {
            Category = m.Category,
            PONO = GetPONo(m, m.Category)
        };

You can take a look at my answer to a similar question here

Answer 2

You can use ternary operator to do different subquery for PONO depending on category value

var model = (from p in db.PoDetails
             select new porders
             {
                 Category = p.Category,
                 PONO = p.Category == "B" ?
                        (from pord in db.Porders 
                         where pord.Id == p.PoId 
                         select pord.No).FirstOrDefault() :
                        (from porder in db.Porders
                         where porder.Id == (from rec in db.RecommendResources 
                                             where rec.Id == p.BibId 
                                             select rec.PoId).FirstOrDefault()
                         select porder.No).FirstOrDefault()
             }).ToList();

Also you can create stored procedure for that.