LINQ左联接仅在ActionResult中起作用 [英] LINQ left join only works in the ActionResult

问题描述

我正在尝试与MVC，Linq，剃须刀等保持同步.有些事情在起作用，而另一些却死胡同，这确实让我感到沮丧.这是我无法解决的第一个问题.在这里问这个问题之前，我已经研究了所有可能的线索，这是我的第一个问题，所以请放轻松.我确定答案就在我的面前，但是我看不到它.我可以输入14页尝试过的内容，但我试图保持简洁明了.我已经完全弄乱了我的代码，因此我从内存中提取了很多代码，因此，与标点错误无关.我很抱歉错误代码也不正确，因为我也正在从内存中提取错误代码.

I'm trying to get up to speed with MVC, linq, razor, etc. Some things are working and others I just hit dead ends that really discourage me. This is the first one I haven't been able to work out. I've researched every lead I can before asking this question here and it's my first, so please go easy. I'm sure the answer is right in front of my face, but I just can't see it. I could type 14 pages of things I've tried, but I'm trying to keep this concise and to the point. I've completely mangled my code, so I'm pulling much of this from memory and therefore, it's not so much about punctuation errors. I apologize the error codes are not exact as I'm pulling those from memory too.

我在ActionResult中有这个左联接linq查询:

I have this left join linq query in my ActionResult:

public ActionResult Index()
{
    var orders = from o in db.Orders
    join u in db.Users on o.UserId equals u.Id into ou
    where o.UserId == uId
    from o in ou.DefaultIfEmpty()
    select o;

    Return View(orders.ToList());
}

在Index.cshtml中，我通过以下方式调用结果:

In the Index.cshtml I call the results by doing:

@foreach (var item in Model.Orders) {
@item.User.Name //(joined Users table)
@item.OrderNumber //(Orders table)

到目前为止，太好了.但是，我想在索引"页面上显示的不仅仅是此列表.因此，据我所知，我需要一个ViewModel来在同一页面上显示其他几段数据(OrdersTotal等).但是，当我在上面的模型中将linq查询停留在以下位置时:

So far, so good. However, I want to display more than just this list on the Index page. So, near as I can tell, I need a ViewModel to display several other pieces of data (OrdersTotal,etc.) on the same page. However, when I stick my linq query above in my models in a:

public  List<Order> GetOrders()
    {

        var orders = from o in db.Orders
    join u in db.Users on o.UserId equals u.Id into ou
    where o.UserId == uId
    from o in ou.DefaultIfEmpty()
    select o;

        return orders.ToList();
    }

它无法识别联接的Users表，这意味着它不知道cshtml中的item.User.Name.然后，我开始进行无法完成的潘多拉盒装实验，主要是在以下select语句中进行操作:

It doesn't recognize the joined Users table, meaning it doesn't know item.User.Name in the cshtml. I then start a pandora's box of experiments that I can not make work, mainly in the select statement like:

select New  //this complains about anonymous type

或

select New Order(o.OrderNumber,u.Id)  //I can't remember the error here, but red squiggly lines

或

select New Order { OrderNumber = o.OrderNumber, UserName = u.Name}  //VS complains about not referencing the Users table, it wants to put a UserName field or property stub in my model, which if I do that, then it errors in the cshtml again as I'm guessing because I don't have a UserName in my Orders table?

几次，我得到了上面的查询，但是在尝试再次访问cshtml中的Users表时，它引发了异常.我的viewmodel控制器如下所示:

A couple times, I got the above queries to work, but then it throws an exception when trying to access the Users table again in the cshtml. My controller for the viewmodel looks like this:

public ActionResult Index()
{

var ordersummary = new OrdersSummary();
var viewModel = new OrderSummary
  {
    Orders = ordersummary.GetOrders(),
    OrdersTotal = ordersummary.GetOrdersTotal(),
  };

return View(viewModel);
}

我的viewModel模型如下:

My viewModel model looks like this:

public class OrderSummary
    {
        public List<Order> Orders { get; set; }
        public decimal OrdersTotal { get; set; }
        public virtual User Users { get; set; }
    {

我的订单"模型基本上使用相同的公共虚拟用户"Users"遍历数据库中的不同列，这些用户与"ActionResult"中的列表很匹配.

My Order model basically iterates through the different columns in the db with the same public virtual User Users like above which works fine with the list is pulled from the ActionResult.

public class Order
    {

        public int Id { get; internal set; }
        public int UserId { get; set; }
        public int OrderNumber { get; set; }
        ...
        ...
        public virtual User Users { get; set; }
    }

我不禁觉得我在模型或控制器中做的根本是错误的，而不是linq语句的语法.非常感谢您的帮助.

I can't help but think I'm doing something fundamentally wrong in my model or controller rather than in the syntax of the linq statement. Any help is much appreciated.

更新:我现在已经阅读了一些教程，并且我基本上是在这里尝试模仿此页面(从ViewModels开始，尽管我的OrderSummary模型源于上面的ShoppingCart GetCartItems等):

UPDATE: I've gone through a few of the tutorials now and I'm basically trying to mimic this page here (down where it starts at ViewModels, although my OrderSummary Model stems from the ShoppingCart GetCartItems,etc. above):

http://www. asp.net/mvc/tutorials/mvc-music-store/mvc-music-store-part-8

但是，以上教程不需要sql连接.想象一下一个票务系统，其中有票证(订单)和技术人员(用户)，并且您正在尝试将票证分配给技术人员(每张票证有一项技术，但是有些票证可能尚未分配技术人员，也称为null)，而我是我尝试创建的是概述部分，在其中查看所有票证和分配给这些票证的技术人员在表中，然后进一步查看统计信息，例如票证总数，或者最终TechA拥有x票证.因此，我需要来自订单数据库的所有信息，而仅需要来自用户数据库的名称，然后能够在索引"页面上引入多条信息.

However, the above tutorial requires no sql join. Imagine a ticket system where you have tickets (orders) and techs (users) and you're trying to assign tickets to techs (one tech per ticket, but some tickets may not have techs assigned to them yet aka null) and what I'm trying to create is the overview portion where you're looking at all the tickets and the techs assigned to those tickets in a table and then further statistics like Total # of Tickets or maybe eventually TechA has x # of Tickets. So, I need all the info from the orders database and simply the name from the users database and then be able to introduce multiple pieces of info on the Index page.

我注意到某些教程使用一个额外的表来加入Ids.因此，在我的情况下，一个额外的表仅包含OrderId和UserId.这是进行联接(缺少适当术语的联接")的更好方法吗?

I notice some of the tutorials use an extra table to join the Ids. So, in my case an extra table that simply has OrderId and UserId. Is that maybe a better way to do the join ("join" for lack of the proper terminology)?

推荐答案

如果您要使用匿名类型，则可以返回IEnumerable<dynamic>并像以前一样工作；

If you want to use anonymous types, you can return IEnumerable<dynamic> and work pretty much just like before with that;

public IEnumerable<dynamic> GetOrders()
{
    var orders = from order in db.Orders
                 join u in db.Users on order.UserId equals u.Id into ou
                 where order.UserId == uId
                 from user in ou.DefaultIfEmpty()
                 select new { order, user };

    return orders.ToList();
}

var item = GetOrders().First();
item.order.Number        // (Orders table)
item.user.Name           // (joined Users table)

这篇关于LINQ左联接仅在ActionResult中起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！