TechSapphire Wednesday, 2018-10-17, 12:16 PM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • LINQ for Beginners


    Topic covered are :

    • WHERE clause in LINQ
    • LEFT JOIN in LINQ
    • INNER JOIN in LINQ
    • GROUP BY in LINQ
    • ORDER BY in LINA

    Script used is mentioned below:

    from i in Customers select i

    from i in Customers select new
    {
    i.CustomerID,
    i.CompanyName,
    i.ContactName
    }
    from i in Customers
    select  new
    {
    i.CustomerID,
    i.CompanyName,
    Orders=from p in i.Orders
                select new { p.EmployeeID , p.OrderDate, p.ShipName }
    }
    //WHERE clause
    from i in Customers
    where (i.CustomerID=="ALFKI")
    select  new
    {
    i.CustomerID,
    i.CompanyName,
    Orders=from p in i.Orders
                select new { p.EmployeeID , p.OrderDate, p.ShipName }
    }
    from i in Customers
    where i.CustomerID.Contains("A")  
    select  new
    {
    i.CustomerID,
    i.CompanyName,
    Orders=from p in i.Orders
                select new { p.EmployeeID , p.OrderDate, p.ShipName }
    }
    //OR and AND operator
    from i in Customers
    where i.CustomerID.StartsWith("A") &&  i.CustomerID.EndsWith("A")
    select  new
    {
    i.CustomerID,
    i.CompanyName,
    Orders=from p in i.Orders
                select new { p.EmployeeID , p.OrderDate, p.ShipName }
    }
    from i in Customers
    where (i.CustomerID.EndsWith("A"))
    select  new
    {
    i.CustomerID,
    i.CompanyName,
    Orders=from p in i.Orders
                select new { p.EmployeeID , p.OrderDate, p.ShipName }
    }
    //NOTE: Select C# Statement(s)
    // SQL IN clause
    string[] CustomerList = {"FISSA","HILAA","ALFKI"};
    var data=from i in Customers
    where CustomerList.Contains(i.CustomerID)
    select new
    {
    i.CustomerID,
    i.CompanyName,
    i.ContactName
    };
    foreach(var i in data)
    {
    Console.Write(i.CompanyName + "\n");
    }
    //Using orderby
    var data=from i in Customers
            where(i.CompanyName.StartsWith("A"))
            orderby i.CompanyName ascending,i.CustomerID descending
            select new
        {
        i.CustomerID,
        i.CompanyName,
        i.ContactName
        };
    foreach(var i in data)
    {
    Console.Write("ID: " + i.CustomerID.ToString() + " Company Name: " + i.CompanyName + " Contact Name: " + i.ContactName.ToString() + "\n");
    }

    //Using Take and Skip for Paging
    (from i in Customers
    orderby i.CompanyName descending
    select new
        {
        i.CustomerID,
        i.CompanyName,
        i.ContactName
        }).Take(5)
    (from i in Customers
    orderby i.CompanyName descending
    select new
        {
        i.CustomerID,
        i.CompanyName,
        i.ContactName
        }).Skip(5).Take(5)
        
    //More Conditions
    from i in Customers
    select new
    {
    i.CustomerID,
    i.CompanyName,
    Orders=from p in i.Orders
            where p.Freight>1000
                select new {p.OrderDate, p.OrderID,Amount =p.Freight}
    }


    from i in Customers
    where i.Orders.Any(p=>p.Freight>1000)
    select new
    {
    i.CustomerID,
    i.CompanyName,
    Orders=from p in i.Orders
            where p.Freight>1000
                select new {p.OrderDate, p.OrderID,Amount =p.Freight}
    }

    from  i in Customers
    let ds=from p in i.Orders
            where p.Freight>1000
            select new { p.OrderID,p.OrderDate,Amount=p.Freight }
    where ds.Any()
    select new {i.CustomerID,i.CompanyName,Orders=ds}

    //Converting Anonymous type to List
    from  i in Customers
    let ds=(from p in i.Orders
            where p.Freight>1000
            select new { p.OrderID,p.OrderDate,Amount=p.Freight }).ToList()
    where ds.Any()
    select new {i.CustomerID,i.CompanyName,Orders=ds}

    //CROSS Join
    from i in Customers
    from p in Orders
    select i.CompanyName + p.OrderID.ToString()


    //Join using WHERE
    from i in Customers
    from p in Orders
    where i.CustomerID==p.CustomerID
    select i.CompanyName + " Order " + p.OrderID.ToString()

    //INNER Join
    from i in Customers
    join p in Orders on i.CustomerID equals p.CustomerID
    select i.CompanyName + " Order " + p.OrderID.ToString()

    //INNER Join
    from i in Customers
    from p in i.Orders
    select i.CompanyName + " Order " + p.OrderID.ToString()

    //Inner Join 3 Level
    from i in Customers
    from o in i.Orders
    from od in o.OrderDetails
    join  p in Products on od.ProductID equals p.ProductID
    orderby o.OrderID
    select i.CompanyName + " Order " + o.OrderID.ToString() + " Product " + p.ProductName

    //Lazy Binding
    from i in Customers
    select new {i.CompanyName,Order=i.Orders.ToList()}

    //DefaultIfEmpty()
    from i in Customers
    from p in i.Orders.DefaultIfEmpty()
    select new {i.CompanyName,Amount=(Decimal?)p.Freight}

    //Group By
    from i in Customers
    where i.Orders.Count>15
    select i

    from i in Orders
    group i.Freight by i.CustomerID into Sales
    select new
    {
    Key=Sales.Key,
    Amount=Sales.Sum()
    }

    from i in Customers
    from p in i.Orders
    group p.Freight by new {i.CompanyName,i.CustomerID } into Sales
    select new
    {
    key=Sales.Key,
    Amount=Sales.Sum(),
    Count=Sales.Count()
    }

     

    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2018