Christopher Trevor Christopher Trevor - 1 month ago 14
C# Question

Could not translate expression

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.

Answer

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,.... }) 
Comments