LINQ2SQL

aspdotnetcodebook In this post i will show mapping of Linq query with SQL query
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
   <title></title>
   <style type="text/css">
       .style1
       {
           width: 100%;
       }
   </style>
</head>
<body>
   <form id="form1" runat="server">
   <div>
   </div>
   <table class="style1">
       <tr>
           <td>
               <asp:Button ID="btnSelect" runat="server" Text="Select Statement" OnClick="btnSelect_Click" />
           </td>
           <td rowspan="9">
               <asp:TextBox ID="txtOutput" runat="server" TextMode="MultiLine" Height="350px" Width="390px"></asp:TextBox>
           </td>
       </tr>
       <tr>
           <td>
               <asp:Button ID="btnInnerJoin" runat="server" Text="Inner Join" OnClick="btnInnerJoin_Click"
                   Width="152px" />
           </td>
       </tr>
       <tr>
           <td>
               <asp:Button ID="btnLeftJoin" runat="server" Text="Left Join" OnClick="btnLeftJoin_Click"
                   Width="151px" />
           </td>
       </tr>
       <tr>
           <td>
               <asp:Button ID="btnMax" runat="server" Text="Max" OnClick="btnMax_Click" Width="151px" />
           </td>
       </tr>
       <tr>
           <td>
               <asp:Button ID="btnOrderBy" runat="server" Text="OrderBY" Width="151px" OnClick="btnOrderBy_Click" />
           </td>
       </tr>
       <tr>
           <td>
               <asp:Button ID="btnDistinct" runat="server" Text="Distinct" OnClick="btnDistinct_Click"
                   Width="150px" />
           </td>
       </tr>
       <tr>
           <td>
               <asp:Button ID="btnLike" runat="server" Text="Like" Width="149px" OnClick="btnLike_Click" />
           </td>
       </tr>
       <tr>
           <td>
               <asp:Button ID="btnSwitchCase" runat="server" Text="SwitchCase" Width="147px" OnClick="btnSwitchCase_Click" />
           </td>
       </tr>
       <tr>
           <td>
               <asp:Button ID="btnIn" runat="server" Text="In Operator" Width="146px" OnClick="btnIn_Click" />
           </td>
       </tr>
   </table>
   </form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
   StudentDataContext dc = new StudentDataContext();
   protected void Page_Load(object sender, EventArgs e)
   {



   }
   /// <summary>
   /// 
   /// </summary>
   /// <param name="sender"></param>
   /// <param name="e"></param>
   protected void btnSelect_Click(object sender, EventArgs e)
   {

       var querySelect = from s in dc.Students
                         select s;
       txtOutput.Text = querySelect.ToString();
   }
   /// <summary>
   /// 
   /// </summary>
   /// <param name="sender"></param>
   /// <param name="e"></param>
   protected void btnInnerJoin_Click(object sender, EventArgs e)
   {
       var queryInnerJoin = from c in dc.Students
                            join m in dc.Marks
                            on c.StudentID equals m.StudentId
                            select new
                            {
                                StudentId = c.StudentID,
                                StudentName = c.Name,
                                Mark = m.Marks

                            };
       txtOutput.Text = queryInnerJoin.ToString();
   }
   /// <summary>
   /// 
   /// </summary>
   /// <param name="sender"></param>
   /// <param name="e"></param>
   protected void btnDistinct_Click(object sender, EventArgs e)
   {
       var queryDistinct = (from s in dc.Students
                            select s).Distinct();
       txtOutput.Text = queryDistinct.ToString();
   }
   /// <summary>
   /// 
   /// </summary>
   /// <param name="sender"></param>
   /// <param name="e"></param>
   protected void btnLeftJoin_Click(object sender, EventArgs e)
   {
       var queryJoin = from c in dc.Students
                       join m in dc.Marks
                       on c.StudentID equals m.StudentId
                       into StudentMarks
                       from joinedstudent in StudentMarks.DefaultIfEmpty()
                       select new
                       {
                           StudentId = c.StudentID,
                           StudentName = c.Name,
                           Age = c.Age,
                           Marks = joinedstudent.Marks


                       };
       txtOutput.Text = queryJoin.ToString();
   }
   /// <summary>
   /// 
   /// </summary>
   /// <param name="sender"></param>
   /// <param name="e"></param>
   protected void btnMax_Click(object sender, EventArgs e)
   {
       var queryMax = from c in dc.Students

                      where c.Marks.Max(x => x.Marks > 50 || x.Marks < 90)
                      select c;


       txtOutput.Text = queryMax.ToString();
   }
   /// <summary>
   /// 
   /// </summary>
   /// <param name="sender"></param>
   /// <param name="e"></param>
   protected void btnLike_Click(object sender, EventArgs e)
   {
       var queryLike = dc.Students.Where(x => x.Name.Contains("S"));

       txtOutput.Text = queryLike.ToString();

   }
   /// <summary>
   /// 
   /// </summary>
   /// <param name="sender"></param>
   /// <param name="e"></param>
   protected void btnSwitchCase_Click(object sender, EventArgs e)
   {
       var swishCase = from c in dc.Students
                       select new
                       {
                           //ternary operator of switch case
                           StudentName =
                            (
                            c.StudentID == 1 ? "Student1" :
                             c.StudentID == 2 ? "Student2" :
                             c.StudentID == 3 ? "Student3" :
                              "Opps"
                             )

                       };
       txtOutput.Text = swishCase.ToString();
   }
   /// <summary>
   /// 
   /// </summary>
   /// <param name="sender"></param>
   /// <param name="e"></param>
   protected void btnIn_Click(object sender, EventArgs e)
   {
       string[] names = { "Student1", "Student2" };
       var filterdQuery = from c in dc.Students
                          where names.Contains(c.Name)
                          select c;
       txtOutput.Text = filterdQuery.ToString();
   }
   /// <summary>
   /// 
   /// </summary>
   /// <param name="sender"></param>
   /// <param name="e"></param>
   protected void btnOrderBy_Click(object sender, EventArgs e)
   {
       var queryGroup = from c in dc.Students
                        join m in dc.Marks
                        on c.StudentID equals m.StudentId
                        orderby c.Age descending
                        select new
                        {
                            StudentName = c.Name,
                            Age = c.Age,
                            Marks = m.Marks


                        };
       txtOutput.Text = queryGroup.ToString();
   }
}

No comments:

Post a Comment