LINQ查询采取总量和正常列在C＃中的MVC [英] linq query to take aggregate and normal column in c# mvc

问题描述

我有两个表 fooditem（项目编号，ITEMNAME，ITEMPRICE）和另一个评级（项目编号，投票，项ID，用户名）

如何获得票数总和GROUPBY itemid的排名表和ITEMNAME，对应于总评级各食品项目ITEMPRICE。
SQL查询

 选择table2.sum（票），table1.itemname，从表1 table1.itemprice，表2，其中table1.itemid ==由table2.itemid table2.itemid组
 

我开始与这个query.Bad运气！

 列表＆LT; FoodView＆GT;项目=（来自于db.FoodItems
                                   加入db.Ratings B关于a.itemid等于b.itemid
                                   B组由b.itemid为G                                   选择新FoodView
                                   {
                                    ITEMNAME = g.Select（D =＆GT; d.itemname）
                                    ITEMPRICE = g.Select（D =＆GT; d.itemprice）
                                    表决= g.Sum（D =＆GT; d.vote）
                                   }）了ToList（）。
 

FoodViewModel VM =新FoodViewModel {Foodviews =项目};

我的模型

 公共类FoodViewModel
{
    公开名单＆LT; FoodView＆GT; Foodviews {搞定;组; }    公共FoodViewModel（）
    {
        Foodviews =新的List＆LT; FoodView＆GT;（）;
    }
}公共类FoodView
{
    众长？ itemid的{搞定;组; }
    公共字符串ITEMNAME {搞定;组; }    众长？ ITEMPRICE {搞定;组; }    众长？投票{搞定;组; }}
 

解决方案

 请使用以下查询：
 VAR foodViewsList =（从fooditemsŤ
                         加入收视率R ON t.itemid等于r.itemid
                         通过新的{r.itemid，t.itemname，t.itemprice}为G R组
                         选择新FoodView
                         {
                             的itemid = g.Key.itemid，
                             ITEMNAME = g.Key.itemname，
                             ITEMPRICE = g.Key.itemprice，
                             表决= g.Sum（X =＆GT; x.vote）
                         }）了ToList（）。
 

I have two tables fooditem(itemid,itemname,itemprice) and another rating(itemid,vote,itemid,username)

How to get sum of votes groupby itemid in rating table and the itemname ,itemprice corresponding to the total rating on each food item. sql query is

select table2.sum(votes),table1.itemname,table1.itemprice from table1,table2 where table1.itemid==table2.itemid group by table2.itemid

I started with this query.Bad luck!

    List<FoodView> items= (from a in db.FoodItems 
                                   join b in db.Ratings on a.itemid equals b.itemid
                                   group b by  b.itemid into g

                                   select new FoodView
                                   {
                                    itemname=g.Select(d=>d.itemname),
                                    itemprice=g.Select(d=>d.itemprice),
                                    vote=g.Sum(d=>d.vote)
                                   }).ToList();

FoodViewModel vm = new FoodViewModel { Foodviews= items};

My Model is

  public class FoodViewModel
{
    public List<FoodView> Foodviews { get; set; }

    public FoodViewModel()
    {
        Foodviews = new List<FoodView>();
    }
}

public class FoodView
{
    public long? itemid { get; set; }
    public string itemname { get; set; }

    public long? itemprice { get; set; }

    public long? vote { get; set; }

}

解决方案

Please use following query:


 var foodViewsList = (from t in fooditems
                         join r in ratings on t.itemid equals r.itemid
                         group r by new { r.itemid, t.itemname, t.itemprice } into g
                         select new FoodView
                         {
                             itemid = g.Key.itemid,
                             itemname = g.Key.itemname,
                             itemprice = g.Key.itemprice,
                             vote = g.Sum(x => x.vote)
                         }).ToList();

这篇关于LINQ查询采取总量和正常列在C＃中的MVC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！