Linq - 当孩子记录查询由diffrent func ganare时的sum子字段值

I have order - items tables. so I want to display on some grid all the orders info including a col like total_items

我有订单 - 物品表。所以我想在一些网格上显示所有订单信息,包括像total_items这样的col

There is a premetive working way. like this:

有一种预防性的工作方式。像这样:

TotalQuantity = 
  (from i in _db.ProposaItems 
   where i.ProposaID == p.ProposaID select i)
  .Sum(q => q.Quantity)

But this is not the way I want. I want to use 2 funcs:

但这不是我想要的方式。我想使用2个功能:

Function 1:

//BizNet.SqlRepository.Data.ProposalItem        
public IQueryable<ProposaItem> GetItems(Guid ProposaID)
{
  return from i in _db.ProposaItems
         where i.ProposaID == ProposaID
         select i;
}

Function 2.

public void GetProposas()
{
  var x = from p in _db.Proposas
          let t= GetItems(p.ProposaID)
                 .Sum(q => q.Quantity)
          select new 
          {
            ID = p.ProposaID,
            TotalQuantity = t
          };
}

For me its look very simple. But in the line

对我来说它看起来很简单。但在线

x.Count();

The result is Exception.

结果是异常。

"Member access 'Int16 Quantity' of 'BizNet.SqlRepository.Data.ProposaItem' not legal on type 'System.Linq.IQueryable`1[BizNet.SqlRepository.Data.ProposaItem]."

"   at System.Data.Linq.SqlClient.SqlMember.set_Expression(SqlExpression value)\r\n   at System.Data.Linq.SqlClient.SqlFactory.Member(SqlExpression expr, MemberInfo member)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.AccessMember(SqlMember m, SqlExpression expo)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitMember(SqlMember m)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitExpression(SqlExpression expr)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitUnaryOperator(SqlUnary uo)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitExpression(SqlExpression expr)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSimpleExpression(SqlSimpleExpression simple)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitExpression(SqlExpression expr)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitUnaryOperator(SqlUnary uo)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitExpression(SqlExpression expr)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSelect(SqlSelect select)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.VisitSequence(SqlSelect sel)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.VisitScalarSubSelect(SqlSubSelect ss)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.VisitSubSelect(SqlSubSelect ss)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSubSelect(SqlSubSelect ss)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitExpression(SqlExpression expr)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitNew(SqlNew sox)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitExpression(SqlExpression expr)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSelect(SqlSelect select)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitAlias(SqlAlias a)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.VisitSource(SqlSource source)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSelect(SqlSelect select)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitAlias(SqlAlias a)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.VisitSource(SqlSource source)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSelect(SqlSelect select)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitIncludeScope(SqlIncludeScope scope)\r\n   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)\r\n   at System.Data.Linq.SqlClient.SqlBinder.Bind(SqlNode node)\r\n   at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(ResultShape resultShape, Type resultType, SqlNode node, ReadOnlyCollection`1 parentParameters, SqlNodeAnnotations annotations)\r\n   at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations)\r\n   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)\r\n   at System.Data.Linq.DataQuery`1.System.Linq.IQueryProvider.Execute[S](Expression expression)\r\n   at System.Linq.Queryable.Count[TSource](IQueryable`1 source)"

There is some explanation for this?

对此有一些解释?

thanks.

2 个解决方案

#1

I don't think you can call unmapped functions (such as GetItems) in a LINQ to SQL query.

我不认为你可以在LINQ to SQL查询中调用未映射的函数(例如GetItems)。

#2

The GetItems func should be ok in a linqtoSql query because it returns an IQueryable itself. If it was a problem you would most likely get a "not supported" exception instead of the one you are getting.

在linqtoSql查询中,GetItems func应该没问题,因为它返回一个IQueryable本身。如果这是一个问题,你很可能得到一个“不支持”的例外而不是你得到的例外。

But it looks like what you actually want is a list of all the product IDs with their associated quantities summed up. If so, then I think you should look at using linq group instead. As far as I can tell, you could write what you are trying to do in a single query like this:

但看起来您真正想要的是所有产品ID的列表及其相关数量的总和。如果是这样,那么我认为你应该考虑使用linq组。据我所知,你可以在一个查询中写下你想要做的事情,如下所示:

var x = from p in _db.ProposaItems
         group p by p.ProposaID into g
         select new
         {
           ID = g.Key,
           TotalQuantity = g.Sum(c => c.Quantity)
         };