时间戳

首页 > 解决方案 > Linq 投影：DbExpressionBinding 需要一个带有集合 ResultType 的输入表达式

c# - Linq 投影：DbExpressionBinding 需要一个带有集合 ResultType 的输入表达式

问题描述

以下 linq 查询运行良好，直到我决定'SerialNoInvoiceOrdrelineDeliveryCloses'使用 Linq 投影将 My ViewModel 中的属性更改为 List 但是，当我尝试运行查询时：

    public ActionResult test(string sortOrder, string searchString, string currentFilter, int? page) {
        ViewBag.CurrentSort = sortOrder;
        ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "fak_desc" : "";
        ViewBag.NameSortParmtwo = String.IsNullOrEmpty(sortOrder) ? "order_desc" : "";

        string EmailID = Session["Email"].ToString();
        if (searchString != null)
        {
            page = 1;
        }
        else
        {
            searchString = currentFilter;
        }

        ViewBag.CurrentFilter = searchString;

        var v = (from cbr in db.Contact_Business_Relation
                 join c in db.Contact on cbr.Contact_No_ equals c.Company_No_
                 join sa in db.Sales_Invoice_Header on cbr.No_ equals sa.Sell_to_Customer_No_
                 join sih in db.Sales_Invoice_Line on sa.No_ equals sih.Document_No_
                 join item in db.Item_Ledger_Entry on sih.No_ equals item.Item_No_

                 where c.E_Mail == EmailID
                 &&
                 sih.Type == 2
                 &&
                 item.Source_Type == 1
                 &&
                 item.Document_Type == 1
                 &&
                 item.Entry_Type == 1
                 select new ClosedOrders
                 {
                     SalesInvoiceQuantity = db.Sales_Invoice_Line.Where(l => l.Document_No_ == sa.No_).Select(l => l.Quantity).DefaultIfEmpty(0).Sum(),
                     CreatedDate = sa.Posting_Date,
                     DeliveryAddress = sa.Ship_to_Address + ", " + sa.Ship_to_Post_Code + " " + sa.Ship_to_City + ", " + sa.Ship_to_Country_Region_Code,
                     Reference = sa.External_Document_No_,
                     OrderNumber = sa.Order_No_,
                     Fakturanummer = sa.No_,
                     Total = 0,
                     AntalAfsendteVarer = 0,
                     AntalVarer = 0,
                     varnummer = sih.No_,
                     SerialNoInvoiceOrdrelineDeliveryCloses = (from Serial_No moc in item.Serial_No_

                                                               select new Serial_No
                                                               {
                                                                   Snumber = moc.Snumber
                                                               }).ToList()
                 }).Distinct();


        if (!String.IsNullOrEmpty(searchString))
        {
            v = v.Where(s => s.Fakturanummer.Contains(searchString)
                                   || s.OrderNumber.Contains(searchString));
        }
        switch (sortOrder)
        {
            case "fak_desc":
                v = v.OrderByDescending(s => s.Fakturanummer);
                break;
            case "order_desc":
                v = v.OrderByDescending(s => s.OrderNumber);
                break;
            default:
                v = v.OrderBy(s => s.CreatedDate);
                break;
        }

        int pageSize = 3;
        int pageNumber = (page ?? 1);
        return View(v.ToPagedList(pageNumber, pageSize));
    }

但是当它尝试执行查询时，它会抛出错误：

DbExpressionBinding 需要一个带有集合 ResultType 的输入表达式。参数navn：输入

这是我的视图模型：

public class ClosedOrders
{

    public DateTime CreatedDate { get; set; }
    public string OrderNumber { get; set; }
    public string DeliveryAddress { get; set; }
    public string Reference { get; set; }
    public decimal AntalVarer { get; set; }
    public decimal AntalAfsendteVarer { get; set; }
    public decimal Total { get; set; }
    public string Fakturanummer { get; set; }
    public decimal SalesInvoiceQuantity { get; set; }
    public string Email { get; set; }
    public List<Serial_No> SerialNoInvoiceOrdrelineDeliveryCloses { get; set; }
    public string varnummer { get; set; }

}

public class Serial_No
{
    public string Snumber { get; set; }
}

这是我的观点：

@model PagedList.IPagedList<Modelnamespace.Models.ClosedOrders>
@using PagedList.Mvc;

@using (Html.BeginForm("test", "Account", FormMethod.Get))
{
    <p>
        Find by name: @Html.TextBox("SearchString", ViewBag.CurrentFilter as string)
        <input type="submit" value="Search" />
    </p>
}
<table class="table">
    <tr>
        <th>
            @Html.ActionLink("Fakturanummer", "test", new { sortOrder = ViewBag.NameSortParm, currentFilter = ViewBag.CurrentFilter })
        </th>
        <th>
            CreatedDate
        </th>
        <th>
            @Html.ActionLink("OrderNumber", "test", new { sortOrder = ViewBag.NameSortParmtwo, currentFilter = ViewBag.CurrentFilter })
        </th>
        <th>serial</th>
        <th>vare</th>
    </tr>


    @foreach (var item in Model)
    {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Fakturanummer)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.OrderNumber)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.CreatedDate)
        </td>
        <td>  @Html.DisplayFor(modelItem => item.SerialNoInvoiceOrdrelineDeliveryCloses)</td>
        <td>  @Html.DisplayFor(modelItem => item.varnummer)</td>
    </tr>
    }

</table>
<br />
Page @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) of @Model.PageCount

@Html.PagedListPager(Model, page => Url.Action("test",
    new { page, sortOrder = ViewBag.CurrentSort, currentFilter = ViewBag.CurrentFilter }))

谁能帮助我或指出我正确的方向！提前致谢

标签： c#asp.net-mvcentity-frameworklinqlinq-to-entities

解决方案

我相信这部分造成了所有麻烦：

SerialNoInvoiceOrdrelineDeliveryCloses =
  (from Serial_No moc in item.Serial_No_
   select new Serial_No
   {
       Snumber = moc.Snumber
   }).ToList()

问题是，这是查询的一部分，需要翻译成 SQL 语句才能对数据库执行。并且要么new Serial_No或ToList根本不能翻译成SQL。因此，这个神秘的错误。这种情况与在此线程中观察到的情况非常相似。

要解决此问题，您可能需要将数据加载到某个中间对象中，然后转换为ClosedOrders实例。

推荐阅读