LINQ - External Data and Expression Trees

Question

First message. Sorry if I missed something. I have a .NET MVC program that accepts any number of filters from a user and then tries to extract data from a model based on these filters. I had success with Dynamic LINQ and Expression Trees, but only when evaluating the fields of my parent model (insured). I cannot figure out how to correctly access the child model (Quote) through LINQ, which is linked to the Insured model using a foreign key relationship. My db models:

    public class Insured {
        [Key]
        [Column(Order = 0)]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public long INS_CLIENT_ID { get; set; }

        [Key]
        [Column(Order = 1)]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public string INS_AGENT_ID { get; set; }

        [Key]
        [Column(Order = 2)]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public long INS_UNQ_ID { get; set; }

        [Column("INS_NAME")]
        public string INS_NAME { get; set; }

        [ForeignKey("QTE_CLIENT_ID, QTE_AGENT_ID, QTE_INS_UNQ_ID")]
        public virtual ICollection<Quote> TBL_QTE { get; set; }
    }

    public class Quote {
        [Key]
        [Column(Order = 0)]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public long QTE_CLIENT_ID { get; set; }

        [Key]
        [Column(Order = 1)]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public string QTE_AGENT_ID { get; set; }

        [Key]
        [Column(Order = 2)]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public long QTE_INS_UNQ_ID { get; set; }

        [Key]
        [Column(Order = 3)]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public long QTE_NUMBER { get; set; }

        [Column("QTE_LOB")]
        public string QTE_LOB { get; set; }

        [Column("QTE_CO_CODE")]
        public string QTE_CO_CODE { get; set; }

    }

Then I have LINQ in my controller file that populates the View model:

m.Insureds = (from i in repository.Insureds
              orderby i.INS_NAME
              select i).Where(GetWhere(s));

And my GetWhere function looks like this (which accepts a different search model for the search):

private Expression<Func<Insured, bool>> GetWhere(SearchOptions s) {
   ParameterExpression pe = Expression.Parameter(typeof(Insured));
   Expression left = Expression.Call(Expression.PropertyOrField(pe, "INS_AGENT_ID"), "ToUpper", null);
   Expression right = Expression.Constant(s.AgentId.ToUpper());
   Expression e1 = Expression.Equal(left, right);

   return Expression.Lambda<Func<Insured, bool>>(e1, pe);
}

, . , TBL_QTE. ...

m.Insureds = (from i in repository.Insureds
              where i.TBL_QTE.AsQueryable<Quote>().All<Quote>(GetLob(s))
              orderby i.INS_NAME
              select i);

GetLob...

private Expression<Func<Quote, bool>> GetLob(SearchOptions s) {
    ParameterExpression pe = Expression.Parameter(typeof(Quote));
    Expression left = Expression.Call(Expression.PropertyOrField(pe, "QTE_LOB"), "ToUpper", null);
    Expression right = Expression.Constant(s.LOBCode.ToUpper());
    Expression e1 = Expression.Equal(left, right);

    return Expression.Lambda<Func<Quote, bool>>(e1, pe);
}

... , Where Expression.Lambda, . , TBL_QTE . , , , 1025:

i.TBL_QTE.AsQueryable<Quote>().All<Quote>(GetLob(s))

Answer 1

, "let" # linq ,

( LinqPad)

p let spanishOrders = p.OrderDetails                        .Where(o = > o.Order.ShipCountry == "" ) spanishOrders.Any() orderby p.ProductName {
    p.ProductName,
    TotalValue = spanishOrders.Sum(o >= > o.UnitPrice * o.Quantity) }