Andre Lashley Andre Lashley - 5 months ago 123
SQL Question

Unable to create a constant value of type 'AddressModel'. Only primitive types or enumeration types are supported in this context

In the EF query below, any time an child contact address field is assigned to the

Address
property of the
TaxReceiptsWithChargesModel
, I get the error message:


"Cannot create constant value of type 'AddressModel'"


If I comment out the line that assigns the address, then the error does not occur. What could the issue be?

Edit: Typically, the causes of this problem I've seen elsewhere have to do with using the contains or equals LINQ methods, but the cause of this particular issue seems to lie elsewhere.

Here are the relevant sections of code:

//GetChildContactAddress
var childContactAddress = lgcCcpModel.ChildContacts
.Where(cc => cc.UUID == ltrm.ChildContactId)
.Select(cc => new AddressModel()
{
Address1 = cc.Address.STREET,
City = cc.Address.CITY,
Province = cc.Address.PROVINCE,
PostalCode = cc.Address.POSTALCODE
}).FirstOrDefault();

//Create the Tax Receipt Model with the Charge List
return unitOfWork.LegacyTaxReceiptStore.GetQuery()
.Where(ltr => ltr.LegacyTaxReceiptId == ltrm.LegacyTaxReceiptId)
.Select(c => new TaxReceiptsWithChargesModel()
{
LegacyTaxReceiptId = ltrm.LegacyTaxReceiptId,
ChildContactId = ltrm.ChildContactId,
ChildContact = ltrm.ChildContact,
EmailAddress = ltrm.EmailAddress,
ChildId = ltrm.ChildId,
ChildName = ltrm.ChildName,
ChargesTotal = ltrm.ChargesTotal,
TaxReceiptAmount = ltrm.TaxReceiptAmount.Value,
TaxReceiptYear = ltrm.TaxReceiptYear,
Address = childContactAddress,
ReceiptNumber = $"{ltrm.TaxReceiptYear}-{ltrm.LegacyTaxReceiptId.ToString().PadLeft(6, '0')}",
Charges = taxReceiptChargesModelList,
}).FirstOrDefault();


public class TaxReceiptsWithChargesModel : ITaxReceiptsModel
{
public int LegacyTaxReceiptId { get; set; }
public string ChildContactId { get; set; }
public string ChildContact { get; set; }
public string EmailAddress { get; set; }
public IAddressModel Address { get; set; }
public decimal? OpeningBalance { get; set; }
public decimal? InvoicesTotal { get; set; }
public decimal? PaymentsTotal { get; set; }
public string ChildId { get; set; }
public string ChildName { get; set; }
public decimal? ChargesTotal { get; set; }
public decimal? TaxReceiptAmount { get; set; }
public string State { get; set; }
public int TaxReceiptYear { get; set; }
public string ReceiptNumber { get; set; }
public int? BinaryDocumentId { get; set; }
public List<TaxReceiptsChargesModel> Charges { get; set; }
}

public interface IAddressModel
{
string Address1 { get; set; }
string Address2 { get; set; }
string City { get; set; }
string Country { get; set; }
string PostalCode { get; set; }
string Province { get; set; }
}

Answer

That is because childContactAddress object (and also taxReceiptChargesModelList) is already in memory and when you try to assign a complex object in the projection of your second query, the Linq provider can't translated that object to SQL. One option can be call AsEnumerable extension method:

  return unitOfWork.LegacyTaxReceiptStore.GetQuery()
        .Where(ltr => ltr.LegacyTaxReceiptId ==ltrm.LegacyTaxReceiptId)
        .AsEnumerable()
        .Select(c => new TaxReceiptsWithChargesModel()
        {
            LegacyTaxReceiptId = ltrm.LegacyTaxReceiptId,
            ChildContactId = ltrm.ChildContactId,
            ChildContact = ltrm.ChildContact,
            EmailAddress = ltrm.EmailAddress,
            ChildId = ltrm.ChildId,
            ChildName = ltrm.ChildName,
            ChargesTotal = ltrm.ChargesTotal,
            TaxReceiptAmount = ltrm.TaxReceiptAmount.Value,
            TaxReceiptYear = ltrm.TaxReceiptYear,
            Address = childContactAddress,
            ReceiptNumber = $"{ltrm.TaxReceiptYear}-{ltrm.LegacyTaxReceiptId.ToString().PadLeft(6, '0')}",
            Charges = taxReceiptChargesModelList, 
        }).FirstOrDefault();

Update

Your issue can be also solve this way:

var result=unitOfWork.LegacyTaxReceiptStore.GetQuery()
            .FirstOrDefault(ltr => ltr.LegacyTaxReceiptId ==ltrm.LegacyTaxReceiptId);

return new TaxReceiptsWithChargesModel()
        {
            LegacyTaxReceiptId = result.LegacyTaxReceiptId,
            ChildContactId = result.ChildContactId,
            ChildContact = result.ChildContact,
            EmailAddress = result.EmailAddress,
            ChildId = result.ChildId,
            ChildName = result.ChildName,
            ChargesTotal = result.ChargesTotal,
            TaxReceiptAmount = result.TaxReceiptAmount.Value,
            TaxReceiptYear = result.TaxReceiptYear,
            Address = childContactAddress,
            ReceiptNumber = $"{result.TaxReceiptYear}-{result.LegacyTaxReceiptId.ToString().PadLeft(6, '0')}",
            Charges = taxReceiptChargesModelList,
        };