Linq Issue dealing with null values in select

Question

I have the following entities in my solution

public class UtilityAccount : IObjectWithState 
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid UtilityAccountID { get; set; }
    public string Account { get; set; }
    [ForeignKey("Person")]
    public Guid PersonID { get; set; }
    public virtual Person Person { get; set; }
    public string ForeignID { get; set; }
    [NotMapped]
    public ObjectState ObjectState { get; set; }

    public virtual ICollection<Utility> Utilities { get; set; }

    public UtilityAccount()
    {
        Utilities = new List<Utility>();
    }
}

public class Utility : IObjectWithState 
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid UtilityID { get; set; }
    [ForeignKey("UtilityAccount")]
    public Guid UtilityAccountID { get; set; }
    public virtual UtilityAccount UtilityAccount { get; set; }
    public Guid? ServiceAddressID { get; set; }
    [ForeignKey("ServiceAddressID")]
    public virtual Address ServiceAddress { get; set; }
    [NotMapped]
    public ObjectState ObjectState { get; set; }
    public double CurrentBalance { get; set; }
    public double? PendingPaymentTotal { get; set; }
    public string ForeignID { get; set; }
    [ForeignKey("UtilityType")]
    public Guid UtilityTypeID { get; set; }
    public virtual UtilityType UtilityType { get; set; }
    public virtual ICollection<UtilityBill> UtilityBills { get; set; }
    public virtual ICollection<IncomingUtilityPayment> IncomingPayments { get; set; }

    public Utility()
    {
        UtilityBills = new List<UtilityBill>();
        IncomingPayments = new List<IncomingUtilityPayment>();
    }
}

public class IncomingUtilityPayment : IObjectWithState
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid IncomingPaymentID { get; set; }
    public string ForeignID { get; set; }
    [ForeignKey("Utility")]
    public Guid UtilityID { get; set; }
    public virtual Utility Utility { get; set; }
    public DateTime PaymentDate { get; set; } 

    public IncomingPaymentStatus IncomingPaymentStatus { get; set; }
    public double? UtilityAmount { get; set; }
    public double? ConvenienceFee { get; set; }
    public double? TotalAmount { get; set; }

    public string AuthCode { get; set; }
    public string AuthReference { get; set; }
    public string TenderType { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int PaymentIdent { get; set; }

    [NotMapped]
    public ObjectState ObjectState { get; set; }
}

My problem is that I am trying to use Linq to retrieve information about a UtilityAccount and I am running into issues with the IncomingPayments for a Utility. Below is the select statement I am trying to use.

returnVal = repo.AllIncluding(o => o.Person, o => o.Utilities, o => o.Utilities.Select(p => p.UtilityType), o => o.Person.BillingAddress, o => o.Utilities.Select(p => p.ServiceAddress), o => o.Utilities.Select(p => p.IncomingPayments.Where(q => q.IncomingPaymentStatus == IncomingPaymentStatus.Pending || q.IncomingPaymentStatus == IncomingPaymentStatus.Processed )));

Everything ran fine until I added this clause to the statement.

o => o.Utilities.Select(p => p.IncomingPayments.Where(q => q.IncomingPaymentStatus == IncomingPaymentStatus.Pending || q.IncomingPaymentStatus == IncomingPaymentStatus.Processed ))

I think my issue ends up being something I am writing wrong in my Linq clause. The error I am getting is

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties.

Parameter name: path

I can use the following statement with no issues

o => o.Utilities.Select(p => p.IncomingPayments)

as soon as I add the where clause in I get the error

score 0 · Answer 1 · answered Apr 24 '13 at 16:24

0

I'm not familiar with EntityFramework nor linq-to-entities, but if it's just like linq-to-object you can:

add a .Where(p => p.IncomingPayments != null) before chaining with your .Select() like this

o.Utilities.Where(p => p.IncomingPayments != null)
           .Select(p => p.IncomingPayments.Where(q => q.IncomingPaymentStatus == IncomingPaymentStatus.Pending || q.IncomingPaymentStatus == IncomingPaymentStatus.Processed))

The result will be a nested IEnumerable, i.e. IEnumerable<IEnumerable<IncomingUtilityPayment>>

If you actually need a IEnumerable<IncomingUtilityPayment> then .SelectMany() come in to play.

o.Utilities.Where(p => p.IncomingPayments != null)
           .SelectMany(p => p.IncomingPayments)
           .Where(q => q.IncomingPaymentStatus == IncomingPaymentStatus.Pending || q.IncomingPaymentStatus == IncomingPaymentStatus.Processed)

Hope this help

answered Apr 24 '13 at 16:24

runTarm

11,537
1
37
37

I think this would solve my problem but when I use the code you provided I get the following error The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. Parameter name: path – Dave Wade Apr 24 '13 at 17:33
It seems like SelectMany does not work in this situation. It makes me use Select maybe because it is within an include statement. If I do not have a where clause in the statement it works fine but as soon as I add the where I get the error list above – Dave Wade Apr 24 '13 at 18:47
It seems like the Include() cannot contains a filtering (Where cause). http://stackoverflow.com/questions/11496681 – runTarm Apr 25 '13 at 04:23

Linq Issue dealing with null values in select

1 Answers1