Could not translate expression

Question

I am getting the following error: "Could not translate expression..." when I try to return a list of enquiry entities, converted to a custom class.

I am very new to using link in this way, in the past I would use stored procedure in SQL and just import them as methods but I am trying to convert these.

My method that returns the list is:

public static List<EnquiryData> GetAllEnquiries()
{
    var GridData = from a in Global.AcepakSalesPortal.Enquiries
                   join Cust in Global.AcepakSalesPortal.Customers
                       on a.CustomerID equals Cust.CustomerID into CustGroup
                   from b in CustGroup.DefaultIfEmpty()
                   join Pros in Global.AcepakSalesPortal.Prospects
                       on a.ProspectID equals Pros.ProspectID into ProsGroup
                   from c in ProsGroup.DefaultIfEmpty()
                   join Users in Global.AcepakSalesPortal.Users
                       on a.ResponsiblePartyID equals Users.UserID into UserGroup
                   from d in UserGroup.DefaultIfEmpty()
                   join Qt in Global.AcepakSalesPortal.Quotes
                       on a.QuoteID equals Qt.QuoteID into QuoteGroup
                   from e in QuoteGroup.DefaultIfEmpty()
                   join Usr in Global.AcepakSalesPortal.Users
                       on e.CreatedBy equals Usr.UserID into UsrGroup
                   from f in UsrGroup.DefaultIfEmpty()
                   join EnqCat in Global.AcepakSalesPortal.EnquiryCategories
                       on a.EnquiryCategoriesID equals EnqCat.EnquiryCatID into CatGroup
                   from g in CatGroup.DefaultIfEmpty()
                   join Clsd in Global.AcepakSalesPortal.Users
                       on a.ClosedBy equals Clsd.UserID into ClsdGroup
                   from h in ClsdGroup.DefaultIfEmpty()
                   orderby a.Created descending
                   select new EnquiryData
                   {
                       EnquiryID = a.EnquiryID,
                       ResponsiblePartyID = a.ResponsiblePartyID,
                       EnquiryNo = "ENQ" + a.EnquiryID.ToString().PadLeft(7, '0'),
                       EType = a.CustomerID.HasValue ? "C" : "P",
                       EnqCat = g.Code + " - " + g.Category,
                       ContactPerson = a.ProspectID.HasValue ? c.ContactPerson : "NOT INTEGRATED YET",
                       ContactNumber = a.ProspectID.HasValue ? c.ContactNum : "NOT INTEGRATED YET",
                       ContactEmail = a.ProspectID.HasValue ? c.ContactEmail : "NOT INTEGRATED YET",
                       Company = a.CustomerID.HasValue ? b.Name : c.CompanyName,
                       Description = a.Description,
                       AssignedTo = d.Name,
                       AddressBy = a.AddressBy,
                       EnquiryDate = a.Created,
                       EStatus = a.Closed.HasValue ? "Closed" : a.QuoteID.HasValue ? "Quoted" : "Open",
                       QuotedOn = a.QuoteID.HasValue ? e.Created.ToShortDateString() : "N/A",
                       QuotedBy = a.QuoteID.HasValue ? f.Name : "N/A",
                       QuoteNum = a.QuoteID.HasValue ? e.QuoteID.ToString().PadLeft(7, '0') : "N/A",
                       ClosedOn = a.Closed.HasValue ? a.Closed.Value.ToShortDateString() : "N/A",
                       ClosedBy = a.Closed.HasValue ? h.Name : "N/A",
                       Reason = a.Closed.HasValue ? a.ClosedReason : "N/A"
                   };

    return GridData.ToList();
}

And the custom class is:

public class EnquiryData
{
    public int EnquiryID { get; set; }
    public int ResponsiblePartyID { get; set; }
    public string EnquiryNo { get; set; }
    public string EType { get; set; }
    public string EnqCat { get; set; }
    public string ContactPerson { get; set; }
    public string ContactNumber { get; set; }
    public string ContactEmail { get; set; }
    public string Company { get; set; }
    public string Description { get; set; }
    public string AssignedTo { get; set; }
    public DateTime AddressBy { get; set; }
    public DateTime EnquiryDate { get; set; }
    public string EStatus { get; set; }
    public string QuotedOn { get; set; }
    public string QuotedBy { get; set; }
    public string QuoteNum { get; set; }
    public string ClosedOn { get; set; }
    public string ClosedBy { get; set; }
    public string Reason { get; set; }
}

My question is 2 fold 1. Is there a better ways to join tables together in Linq than I am doing above? 2. What could be causing the error, I dont mind figuring it out but not sure how to even approach this.

EDIT: This is most definitely not a duplicate of the mentioned question. The only similarity between the 2 is the use of shortdatestring, however the error message I receive is completely different to that of the other question.

can you tell what is the issue when you just do like this `select a` instead of the custom class mapper.tell us ? — Sampath, Oct 26 '16 at 08:43
When using select a it brings the records through fine without any exceptions. So I am guessing it is some conversion that it is not happy with. — ThatChris, Oct 26 '16 at 08:47
context.Database.Log = (sw)=>Debug.WriteLine(sw); you can include this line before declaration of your query. and check in output window in VS for translated query and execption. Where context is your DbContext Name — Raghu, Oct 26 '16 at 08:50

score 1 · Accepted Answer · answered Oct 26 '16 at 08:51

You have used lot of c# methods which are not known by the SQL.Hence you can retrieve all the columns as shown below and then do your custom mapping on the memory as you wish.

var GridData = (from a in Global.AcepakSalesPortal.Enquiries
                   join Cust in Global.AcepakSalesPortal.Customers
                       on a.CustomerID equals Cust.CustomerID into CustGroup
                   from b in CustGroup.DefaultIfEmpty()
                   join Pros in Global.AcepakSalesPortal.Prospects
                       on a.ProspectID equals Pros.ProspectID into ProsGroup
                   from c in ProsGroup.DefaultIfEmpty()
                   join Users in Global.AcepakSalesPortal.Users
                       on a.ResponsiblePartyID equals Users.UserID into UserGroup
                   from d in UserGroup.DefaultIfEmpty()
                   join Qt in Global.AcepakSalesPortal.Quotes
                       on a.QuoteID equals Qt.QuoteID into QuoteGroup
                   from e in QuoteGroup.DefaultIfEmpty()
                   join Usr in Global.AcepakSalesPortal.Users
                       on e.CreatedBy equals Usr.UserID into UsrGroup
                   from f in UsrGroup.DefaultIfEmpty()
                   join EnqCat in Global.AcepakSalesPortal.EnquiryCategories
                       on a.EnquiryCategoriesID equals EnqCat.EnquiryCatID into CatGroup
                   from g in CatGroup.DefaultIfEmpty()
                   join Clsd in Global.AcepakSalesPortal.Users
                       on a.ClosedBy equals Clsd.UserID into ClsdGroup
                   from h in ClsdGroup.DefaultIfEmpty()
                   orderby a.Created descending
                   select a).ToList()

After that do your custom class mapping here :

var list= GridData.Select(a=>new EnquiryData{EnquiryID = a.EnquiryID,.... })

score 0 · Answer 2 · answered Oct 26 '16 at 08:46

1.When these query executes, linq complier needs to transform this linq queries to SQL query.Most of the methods implementing IQuerable inferface, can be convertable. but C# methods like ToString() , ToShortDateString() could not be able to convert by Linq complier. so you get 'Could not translate expression..'.

score -1 · Answer 3 · answered Oct 26 '16 at 08:49

-1

You could try:

var GridData = from a in Global.AcepakSalesPortal.Enquiries
                   join Cust in Global.AcepakSalesPortal.Customers on a.CustomerID equals Cust.CustomerID 
                   join Pros in Global.AcepakSalesPortal.Prospects on a.ProspectID equals Pros.ProspectID 
                   join Users in Global.AcepakSalesPortal.Users on a.ResponsiblePartyID equals Users.UserID

etc..

answered Oct 26 '16 at 08:49

Lidaranis

765
3
9

Would that not result in a inner join rather than a left? At least that was my understanding? – ThatChris Oct 26 '16 at 08:50

Could not translate expression

3 Answers3