LINQ查询先进的多列 [英] Linq advanced queries with multiple columns

问题描述

在我的ASP.NET项目我有一个数据表命名BrowserStats其中包含针对不同用户不同的浏览器清单。
我想按自己的ID用户，并显示使用LINQ特定的浏览器（Chrome浏览器，火狐，IE）接入的数量。结果
我的数据表（BrowserStats）是如下：

用户ID |浏览器
----------------------------
1 |铬32.0
1 |铬30.0
1 |铬33.0
1 |火狐-20.0
1 |火狐-26.0
1 | Safari浏览器
1 | IE-9
1 | IE-10
2 |铬31.0
2 |铬32.0
2 | IE-10
2 |火狐-22.0
2 |火狐-26.0

我的输出表应该是：

用户ID |镀铬|火狐| IE浏览器|其他
-----------------------------------------------
1 | 3 | 2 | 2 | 1
2 | 2 | 2 | 1 | 0

1. 如何将在LINQ查询为该输出？


2. LINQ是一个更快的方法，或者我应该写一个存储过程与此查询数据库，并从C＃调用它？


3. 有没有什么好的教程上的高级LINQ查询？

解决方案

您可以在LINQ使用嵌套组： -

  VAR的查询=从浏览器中browesers
                        集团通过浏览器进入browser.UserID用户组
                        从countGroup在
                            （从用户组眉头
                             通过新组眉头
                                 {
                                     镀铬= brow.Browser.Contains（铬），
                                     火狐= brow.Browser.Contains（火狐），
                                     IE = brow.Browser.Contains（IE）
                                 }进入测试
                                     新选择
                                     {
                                         ChromeCount = test.Key.Chrome？ test.Count（）：0，
                                         FirefoxCount = test.Key.Firefox？ test.Count（）：0，
                                         IECount = test.Key.IE？ test.Count（）：0，
                                         OthersCount =（test.Key.Chrome＆安培;！＆安培;！test.Key.Firefox和放大器;＆安培;！test.Key.IE）？ test.Count（）：0
                                     }
                                 ）
                        通过UserGroup.Key组countGroup;
 

我使用了下列类型： -

 公共类BrowserInfo
    {
        公众诠释用户名{搞定;组; }
        公共字符串浏览器{搞定;组; }
    }
 

下面是工作小提琴。

In my ASP.NET Project I have a data table named BrowserStats which contains different browser list for different users. I want to group the users by their id and shows the number of access with specific browsers(Chrome,Firefox,IE) using linq.
My Data Table(BrowserStats) is as following:

UserId      |   Browser
----------------------------
1           |   Chrome-32.0
1           |   Chrome-30.0
1           |   Chrome-33.0
1           |   Firefox-20.0
1           |   Firefox-26.0
1           |   Safari 
1           |   IE-9
1           |   IE-10
2           |   Chrome-31.0
2           |   Chrome-32.0
2           |   IE-10
2           |   Firefox-22.0
2           |   Firefox-26.0

My Output Table should be :

UserId      | Chrome |  Firefox |  IE | Others
-----------------------------------------------
1           |   3    |      2   |   2 |     1
2           |   2    |      2   |   1 |     0

1. How would be the query in linq for this output?
2. Is linq a faster way or I should write a stored procedure with this query in database and call it from C#?
3. Is there any good tutorial on Advanced linq queries?

解决方案

You can use Nested group in LINQ:-

var query = from browser in browesers
                        group browser by browser.UserID into UserGroup
                        from countGroup in
                            (from brow in UserGroup
                             group brow by new
                                 {
                                     Chrome = brow.Browser.Contains("Chrome"),
                                     Firefox = brow.Browser.Contains("Firefox"),
                                     IE = brow.Browser.Contains("IE")
                                 } into test
                                     select new
                                     {
                                         ChromeCount = test.Key.Chrome ? test.Count() : 0,
                                         FirefoxCount = test.Key.Firefox ? test.Count() : 0,
                                         IECount = test.Key.IE ? test.Count() : 0,
                                         OthersCount = (!test.Key.Chrome && !test.Key.Firefox && !test.Key.IE) ? test.Count() : 0
                                     }
                                 )
                        group countGroup by UserGroup.Key;

I have Used the following Type:-

public class BrowserInfo
    {
        public int UserID { get; set; }
        public string Browser { get; set; }
    }

Here is the working Fiddle.

这篇关于LINQ查询先进的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！