Linq advanced queries with multiple columns

问题:

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?

回答1:

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.

回答2:

first off, I would consider LINQ slow. LINQ is pretty much a clean way to nest array iteration logic. Only use when the CPU must manipulate static data. or data is CPU generated and there is no backing storage.

that's how I think of it anyways. now for the answer:

I have built a database in SQLServer 2012 Express for Demon-Striation purposes (<- Xanth reference). I used your browsers and made imaginary users. the pivot code should be a stored procedure you call from c#. if you are using VS I can edit for a pure VS(2012) solution because I would prefer to use datasets and add a query to the TableAdapter for using a stored procedure. but this should get you 2/3rds the way there

the User Table::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

the Browser Table:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

the Usage Table Sample (324 row total):::::::::::::::::::::::::::::::::::::::::::::::::::

the Diagram:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

The PIVOT (I added this as a stored procedure):::::::::::::::::::::::::::::::::::::::::::

VS 2012 / WPF implementation::::::::::::::::::

after adding the stored procedure, connecting VS to your database, and adding a Dataset to your project. Drag and drop the stored procedure into your dataset. you can also use the stored procedure without the typed dataset generator. See Here

WPF XAML::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

<Window x:Class="WPFPIVOT.MainWindow"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Title="MainWindow" Height="350" Width="525">
<Grid>
    <DataGrid Name="datagrid" ItemsSource="{Binding}"/>
</Grid>
</Window>

.cs::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;

namespace WPFPIVOT
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
    Pivot pivoted;
    PivotTableAdapters.GetUsageTableAdapter adapter;


    public MainWindow()
    {
        InitializeComponent();

        //this code is the same for WPF and FORMs
        pivoted = new Pivot();
        adapter = new PivotTableAdapters.GetUsageTableAdapter();
        adapter.Fill(pivoted.GetUsage);
        ///////////////////////////////////////////////////////////////


        datagrid.DataContext = pivoted;
    }


}
}

WPF WINDOW:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

(the UserID column defaults to the right most column the select statement could be select p1.UserID,p1[1],... p.[n])

回答3:

if you want to go with LINQ. it dynamic add column and row to datatable. output is datatable format

DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[] { new DataColumn("Browser", Type.GetType("System.String")), new DataColumn("userid", Type.GetType("System.String")) });
        dt.Rows.Add(new object[] {"Chrome-32.0","1" });
        dt.Rows.Add(new object[] { "Chrome-32.0", "1" });
        dt.Rows.Add(new object[] { "Firefox-20.0", "1" });
        dt.Rows.Add(new object[] { "Firefox-26.0", "1" });
        dt.Rows.Add(new object[] { "Safari", "1" });
        dt.Rows.Add(new object[] { "IE-9", "1" });
        dt.Rows.Add(new object[] { "IE-10", "1" });
        dt.Rows.Add(new object[] { "Chrome-31.0", "2" });
        dt.Rows.Add(new object[] { "Chrome-32.0", "1" });
        dt.Rows.Add(new object[] { "IE-10", "1" });
        dt.Rows.Add(new object[] { "Firefox-22.0", "2" });

        DataTable dtOutPut = new DataTable();
        dtOutPut.Columns.Add(new DataColumn("UserID", Type.GetType("System.String")));
        var tableColumnName = dt.AsEnumerable().Select(s => s.Field<string>("Browser").Trim().ToLower().Split('-')[0].Trim()).Distinct();
        foreach (var item in tableColumnName)
        {
            dtOutPut.Columns.Add(new DataColumn(item, Type.GetType("System.String")));
        }

        var usrid = dt.AsEnumerable().Select(s => s.Field<string>("userid").Trim()).Distinct();

        Parallel.ForEach(usrid, (s) => {

            DataRow rec = dtOutPut.NewRow();
            rec["UserID"] = s;
            foreach (var item in tableColumnName)
            {
                rec[item] = dt.AsEnumerable().Where(s1 => s1.Field<string>("Browser").Trim().ToLower().Contains(item) && s1.Field<string>("userid") == s).Count();

            }

            dtOutPut.Rows.Add(rec);
        });

output: