# Using LINQ Queries to Add Data in FlexPivot

This topic covers information about using LINQ queries to add data.

## Content

FlexPivot can use various collections such as LINQ queries to add data. LINQ provides a flexible and efficient data querying model to create new queries within the client application without modifying the database. As a result, FlexPivot control can use LINQ queries as a data source so that end users can create their own views for data analysis.

Complete the following steps to create a LINQ query and use it as a data source for FlexPivot.

1. Create a new Windows Forms Application project in Visual Studio.
2. Drag-and-drop FlexPivotPage control (see FlexPivotpage icon in the Toolbox) onto the form.
3. Switch to the code view and add the following namespaces. 

    ```vbnet
    Imports System.Data.OleDb
    Imports System.Linq
    ```

    ```csharp
    using System.Data.OleDb;
    using System.Linq;
    ```
4. Add the following code in the Forms1.cs constructor to load the data using LINQ query. 

    ```vbnet
    Dim ds = New DataSet()
    For Each table As String In "Products,Categories,Employees,Customers,Orders,Order Details".Split(","c)
        Dim sql As String = String.Format("select * from [{0}]", table)
        Dim da = New OleDbDataAdapter(sql, GetConnectionString())
        da.Fill(ds, table)
    Next table
    ```

    ```csharp
    var ds = new DataSet();
    foreach (string table in "Products,Categories,Employees,Customers,Orders,Order Details".Split(','))
    {
        string sql = string.Format("select * from [{0}]", table);
        var da = new OleDbDataAdapter(sql, GetConnectionString());
        da.Fill(ds, table);
    }
    ```
5. Initialize a connection string to connect with a database installed on your system. This example uses **C1NWind.mdb** file as the database. You can find this file at **Documents\\ComponentOne Samples\\Common** location on your system. In case your database file is kept at a different location then make changes in the path defined in the **GetConnectionString** method. 

    ```vbnet
    Private Shared Function GetConnectionString() As String
        Dim path As String = Environment.GetFolderPath(Environment.SpecialFolder.Personal) & "\ComponentOne Samples\Common"
        Dim conn As String = "provider=microsoft.jet.oledb.4.0;data source={0}\c1nwind.mdb;"
        Return String.Format(conn, path)
    End Function
    Private Sub FlexPivotPage1_Load(sender As Object, e As EventArgs)
    End Sub
    ```

    ```csharp
    static string GetConnectionString()
    {
        string path = Environment.GetFolderPath(Environment.SpecialFolder.Personal) + @"\ComponentOne Samples\Common";
        string conn = @"provider=microsoft.jet.oledb.4.0;data source={0}\c1nwind.mdb;";
        return string.Format(conn, path);
    }
    ```
6. Add the following LINQ query to connect the FlexPivotPage control with the tables loaded from the database. 

    ```vbnet
    Dim q = From detail In ds.Tables("Order Details").AsEnumerable() _
            Join product In ds.Tables("Products").AsEnumerable() On detail.Field(Of Integer)("ProductID") Equals product.Field(Of Integer)("ProductID") _
            Join category In ds.Tables("Categories").AsEnumerable() On product.Field(Of Integer)("CategoryID") Equals category.Field(Of Integer)("CategoryID") _
            Join order In ds.Tables("Orders").AsEnumerable() On detail.Field(Of Integer)("OrderID") Equals order.Field(Of Integer)("OrderID") _
            Join customer In ds.Tables("Customers").AsEnumerable() On order.Field(Of String)("CustomerID") Equals customer.Field(Of String)("CustomerID") _
    ```

    ```csharp
    var q =
       from detail in ds.Tables["Order Details"].AsEnumerable()
       join product in ds.Tables["Products"].AsEnumerable()
           on detail.Field<int>("ProductID") equals product.Field<int>("ProductID")
       join category in ds.Tables["Categories"].AsEnumerable()
           on product.Field<int>("CategoryID") equals category.Field<int>("CategoryID")
       join order in ds.Tables["Orders"].AsEnumerable()
           on detail.Field<int>("OrderID") equals order.Field<int>("OrderID")
       join customer in ds.Tables["Customers"].AsEnumerable()
           on order.Field<string>("CustomerID") equals customer.Field<string>("CustomerID")
       join employee in ds.Tables["Employees"].AsEnumerable()
           on order.Field<int>("EmployeeID") equals employee.Field<int>("EmployeeID")
    ```

    Each table connects to the query by joining its primary key to a given field. For instance, Products table is joined using ProductID, Categories is joined using CategoryID, and so on.
7. Add the following Select statement to build a new anonymous class containing fields specified in query above. Note that the fields may map directly to the table fields or may be calculated. 

    ```vbnet
    Select CaseNew With
      {
        Key .Sales = (detail.Field(Of Short)("Quantity") * CDbl(detail.Field(Of Decimal)("UnitPrice"))) * (1 - CDbl(detail.Field(Of Single)("Discount"))),
        Key .OrderDate = order.Field(Of Date)("OrderDate"),
        Key .Product = product.Field(Of String)("ProductName"),
        Key .Customer = customer.Field(Of String)("CompanyName"),
        Key .Country = customer.Field(Of String)("Country"),
        Key .Employee = employee.Field(Of String)("FirstName") & " " & employee.Field(Of String)("LastName"),
        Key .Category = category.Field(Of String)("CategoryName")}
    ```

    ```csharp
    select new
    {
        Sales = (detail.Field<short>("Quantity") * (double)detail.Field<decimal>("UnitPrice")) * (1 - (double)detail.Field<float>("Discount")),
        OrderDate = order.Field<DateTime>("OrderDate"),
        Product = product.Field<string>("ProductName"),
        Customer = customer.Field<string>("CompanyName"),
        Country = customer.Field<string>("Country"),
        Employee = employee.Field<string>("FirstName") + " " + employee.Field<string>("LastName"),
        Category = category.Field<string>("CategoryName")
    };
    ```
8. Convert the LINQ query to a list using the **ToList()** method and assign the resultant to the [DataSource](/componentone/api/win/online-flexpivot/dotnet-framework-api/C1.Win.FlexPivot.4.8/C1.Win.FlexPivot.C1FlexPivotPage.DataSource.html) property of [FlexPivotPage](/componentone/api/win/online-flexpivot/dotnet-framework-api/C1.Win.FlexPivot.4.8/C1.Win.FlexPivot.C1FlexPivotPage.html) class. **vbnet**

    ```vbnet
    FlexPivotPage1.DataSource = q.ToList()
    ```

    ```csharp
    flexPivotPage1.DataSource = q.ToList();
    ```
9. Create a default view that gets loaded as the initial view when the application runs. 

    ```vbnet
    Dim fp = FlexPivotPage1.FlexPivotPanel.FlexPivotEngine
    fp.BeginUpdate()
    fp.RowFields.Add("Country")
    fp.ColumnFields.Add("Category")
    fp.ValueFields.Add("Sales")
    fp.EndUpdate()
    ```

    ```csharp
    var fp = flexPivotPage1.FlexPivotPanel.FlexPivotEngine;
    fp.BeginUpdate();
    fp.RowFields.Add("Country");
    fp.ColumnFields.Add("Category");
    fp.ValueFields.Add("Sales");
    fp.EndUpdate();
    ```
10. Run the project to observe that the FlexPivotPage control bound to data from tables defined in the **C1NWind.mdb** database file appears with an initial view.