LINQ - Queries and Relationships

Executing a procedure

Executing a procedure can be as simple. Let's say we create a new procedure with the following code

ALTER PROCEDURE dbo.GetAllRoles(@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
AS
SET NOCOUNT ON
SELECT * FROM ROLE
RETURN

We can execute from the front end with the following code
PeopleDataContext db = new PeopleDataContext();
var enRole = db.GetAllRoles();

If you want to bind the result obtained above into a grid control, follow the code below
gvRoles.DataSource = enRole.ToList();
gvRoles.DataBind();

Selective record fetching or Pagination operations

Let's say you want to fetch records for pagination operation. Typically scenario would be we will first
fetch some 50 records, store 40 in memory/cache and display 10 records at a time. Next step would be fetch
next 50 records. So long we would have struggled to get that done. Now, this has become much easier, follow
the code below to acheive this functionality
In the code below, you can set 'nSkip' value programatically, by defining it in a function and, invoking
it with appropriate values

PeopleDataContext db = new PeopleDataContext();
var lstInfo = db.ProjectInfos.Skip(nSkip).Select (p => p).Take(10);
gvMoreInfo.DataSource = lstInfo.ToList();
gvMoreInfo.DataBind();

Managing relationship

One-to-One relationship

LINQ scores heavily in doing such operations. It's so easy for you to acheieve links. Lets say you want to link
a new salary table with person table

structure of salary table is given below
Id int
WorkFromHome int
income float
paidholidays int

Id in this table will the same as in the Person table, used in previous examples. When you drag and drop this
table in the DBML file, you will notice the relationship would be automatically configured by .NET. Doing the
same in some of the contemporary ORM tools can be a 'pain in the butt'.

Our assumption is when we fetch person information, matching data from salary table should also be made
available. Yes, you are right, that's the LINQ works too. And the way we access data is
var personInfo = from p in db.Persons
select new { p.Id, p.FirstName,
p.LastName,
p.Role.Designation,
p.Salary.income,
p.Salary.paidholidays,
p.Salary.WorkFromHome };

gvPersonDetails.DataSource = personInfo.ToList();
gvPersonDetails.DataBind();

In the above sample, you can see the way Salay object is being accessed from the Person object.

More on relationships would be released shortly

LINQ - An introduction

LINQ - DB Operations

LINQ is fun to work with. I was a bit reluctant to start with LINQ, but once in, it took me like forest fire.
Its one of the most interesting things I have worked in the recent past. To start with, LINQ provides an
interface to query objects for data. Objects might contain data in them or they could communicate with DB and
fetch results for you. Basically, LINQ acts like a ORM tool.

I did a descent amount of work in NHibernate, which is yet another ORM tool. I am not going to do a compare and
contrast on these too, but they work almost the same way, except for the point LINQ is slightly faster.

LINQ can be used to query objects, DB's and XML. Here we'd see our operations on DB, while querying objects will be
available as a seperate post, shortly.

I started with a simple web application, that would query DB to perform CRUD operations, extract of that is
deliniated.

Create your data source with tables, and requisite data.
This is the structure of the Person table I used for my example

Id int
FirstName nvarchar(50)
LastName nvarchar(50)
IDRole int

I also have a Role table which has the following format

Id int
Designation nvarchar(50)

Create a new Web/Windows project, Add a new LINQ to SQL class. I created a web application with the name LIQWebSamples
Provide it a useful name. This would result in a dbml file. I named it People.dbml.
Open the datasource in server explorer, drag Person table and drop it in the dbml file. I added a new form CRUDForm.aspx

My web page designer code is appended

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CRUDForm.aspx.cs" Inherits="LIQWebSamples.CRUDForm" %>

<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table cellspacing="15" cellpadding = "10">
<tr>
<td>
<asp:Button ID="btnNew" runat="server" Text="New" onclick="btnNew_Click" />
</td>
<td>
<asp:Button ID="btnEdit" runat="server" Text="Edit" onclick="btnEdit_Click" />
</td>
<td>
<asp:Button ID="btnUpdate" runat="server" Text="Update"
onclick="btnUpdate_Click" />
</td>
<td>
<asp:Button ID="btnDelete" runat="server" Text="Delete"
onclick="btnDelete_Click" />
</td>
</tr>
</table>
<hr />
<table cellspacing="15" cellpadding = "10">
<tr>
<td>
<asp:Label ID="Label1" runat="server" Text="First Name"></asp:Label>
</td>
<td>
<asp:DropDownList ID="ddListFName" runat="server" AutoPostBack="false">
</asp:DropDownList>
</td>
<td>
<asp:Label ID="Label2" runat="server" Text="Last Name"></asp:Label>
</td>
<td>
<asp:DropDownList ID="ddListLName" runat="server" AutoPostBack="false">
</asp:DropDownList>
</td>
<td>
<asp:Button ID="btnSearch" runat="server" Text="Search"
onclick="btnSearch_Click"/>
</td>
</tr>
</table>
<hr />
<br />
<table cellpadding="10" cellspacing="15">
<tr>
<td>
<asp:Label ID="Label3" runat="server" Text="ID"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtID" runat="server"></asp:TextBox><br />
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label4" runat="server" Text="First Name"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtFName" runat="server"></asp:TextBox><br />
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label5" runat="server" Text="Last Name"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtLName" runat="server"></asp:TextBox><br />
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label6" runat="server" Text="Role Id"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtRoleId" runat="server"></asp:TextBox><br />
</td>
</tr>
</table>
<br />
<asp:Label ID="lblStatus" runat="server" Font-Bold="True" Font-Size="Larger" ForeColor="#FFCC00"></asp:Label>
</div>
</form>
</body>
</html>

Congratulations, you have created your entity and object to head on LINQ. Take some moment to browse through
DBML file. It would help you understand how mapping is done

Select operation

Let's do a simple select operation to fill in First Name and Last name field. Ensure you have some valid data
in your DB

As I was telling you, there are 2 ways queries can be executed in LINQ, a longer format or Lambda expressions/
format. We'll see both. Lets take a look at the longer format first.

In all of the samples, you 'll see me using a new object called DataContext. People with ADO/ADO.NET background,
can treat this equivalent to a connection class for communication with DB

        public List GetPeopleByFirstName()
        {
            Debug.WriteLine("First Name, Start Time :" + DateTime.Now.ToString());
            PeopleDataContext db = new PeopleDataContext();
            var pFName = from p in db.Persons select p.FirstName;
            Debug.WriteLine(pFName.ToString());
            return pFName.ToList();
        }

Here we do a similar operation, as above. But fetch last name, using Lambda expression. It's more easy isn't.
There are no performance differences between Lambda expressions and the longer format. But certainly there
are syntactical differences, as you can see. It's much easy doing a Lambda operation then doing a longer format

        public List GetPeopleByLastName()
        {
            Debug.WriteLine("Last Name Start Time :" + DateTime.Now.ToString());
            PeopleDataContext db = new PeopleDataContext();
            //var pLName = from p in db.Persons select p.LastName;
            var pLName = db.Persons.Select(p => p.LastName);
            Debug.WriteLine(pLName.ToString());
            return pLName.ToList();
        }

A sample of filter is attached

        public List GetPersonInfoFromFirstName(string sFName)
        {
            Debug.WriteLine("Search on first name start : " + DateTime.Now.ToString());
            PeopleDataContext db = new PeopleDataContext();
            var lstPeople = from p in db.Persons
                            where p.FirstName == sFName
                            select p;
            Debug.WriteLine(lstPeople.ToString());
            return lstPeople.ToList();
        }

A similar filter using Lambda expression

        public List GetPersonInfoFromLastName(string sLName)
        {
            Debug.WriteLine("Search on last name start : " + DateTime.Now.ToString());
            PeopleDataContext db = new PeopleDataContext();
            var lstPeople = db.Persons.Where(p => p.LastName == sLName).Select(p => p);
            Debug.WriteLine(lstPeople.ToString());
            return lstPeople.ToList();
        }

A sample on multiple filter operation

        public List GetPersonInfoFromFirstAndLastName(string sFName, string sLName)
        {
            Debug.WriteLine("Search on first & last name start : " + DateTime.Now.ToString());
            PeopleDataContext db = new PeopleDataContext();
            var lstPeople = db.Persons.Where(p => p.FirstName == sFName && p.LastName == sLName).Select(p => p);
            Debug.WriteLine(lstPeople.ToString());
            return lstPeople.ToList();
        }

Insert operation

Insert operation is going to be very simple. All that we need to do is create a new object of the entity that
needs to be inserted (in our case, Person class), and pass it on to the datacontext for update operations

        public bool InsertNewPerson(int nId, string sFName, string sLName, int nRoleId)
        {
            //presumed that validations have happened upfront
            Debug.WriteLine("New record insert start : " + DateTime.Now.ToString());
            PeopleDataContext db = new PeopleDataContext();
            Person pers = new Person();
            pers.Id = nId;
            pers.FirstName = sFName;
            pers.LastName = sLName;
            pers.IDRole = nRoleId;
            db.Persons.Add(pers);
            db.SubmitChanges();
            //db.Persons.Add(
            return true;
        }

Update Operation

Very similar to Insert operation. First we load the object that needs to be updated, make changes to the object,
and call SubmitChanges method of DataContext.

        public bool UpdatePerson(int nId, string sFName, string sLName, int nRoleId)
        {
            Debug.WriteLine("Update record start : " + DateTime.Now.ToString());
            PeopleDataContext db = new PeopleDataContext();
            Person per = (Person)db.Persons.Single(p => p.Id == nId);
            per.FirstName = sFName;
            per.LastName = sLName;
            per.IDRole = nRoleId;
            db.SubmitChanges();
            return true;
        }

Delete Operation

Fetch the record to be deleted and invoke Remove Method

        public bool DeletePerson(int nId)
        {
            Debug.WriteLine("Delete record start : " + DateTime.Now.ToString());
            PeopleDataContext db = new PeopleDataContext();
            //if there are more than one person to be deleted, use this code
            //var delPerson = db.Persons.Where(p => p.Id == nId).Select(p=>p);
            var delPerson = db.Persons.Single(p => p.Id == nId);
            //check to see if there are records for deletion
            db.Persons.Remove(delPerson);
            //db.Persons.RemoveAll(delPerson);
            db.SubmitChanges();
            return true;
        }
    }
}

You are done with all basic CRUD operations. I have also attached code behind which will help you see how I
am binding records retreived for display. Hope it helps

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using System.Diagnostics;
using LIQWebSamples.BusinessOperations;

namespace LIQWebSamples
{
    public partial class CRUDForm : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                LoadDataInControls();               
            }
            else
            {

                if (Session["Status"] != null)
                {
                    string sStatus = Session["Status"].ToString();
                    if (sStatus.Equals("UPDATED"))
                    {
                        LoadDataInControls();
                    }
                }
            }
        }

        private void LoadDataInControls()
        {
            Debug.WriteLine("Page Load Start Time :" + DateTime.Now.ToString());
            PeopleOperations op = new PeopleOperations();
            ddListFName.DataSource = op.GetPeopleByFirstName();
            Debug.WriteLine("First Name End Time :" + DateTime.Now.ToString());
            ddListLName.DataSource = op.GetPeopleByLastName();
            Debug.WriteLine("Last Name End Time :" + DateTime.Now.ToString());
            Debug.WriteLine("Page Load - Data Binding, Start Time :" + DateTime.Now.ToString());
            ddListFName.DataBind();
            ddListLName.DataBind();
            ddListFName.Items.Add(" ");
            ddListLName.Items.Add(" ");
            ddListLName.SelectedIndex = 13;
            ddListFName.SelectedIndex = 13;

            Debug.WriteLine("Page Load End Time :" + DateTime.Now.ToString());
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {
            PeopleOperations op = new PeopleOperations();
            List lstPerson = null;
            string sText = ddListFName.SelectedValue;
            sText = ddListFName.SelectedItem.Value;
            if (ddListLName.Text.Trim().Length > 0 && ddListFName.Text.Trim().Length <= 0)             {                 //search for last name                                 lstPerson = op.GetPersonInfoFromLastName(ddListLName.SelectedValue);                 Debug.WriteLine("Search on last name end : " + DateTime.Now.ToString());             }             else if (ddListLName.Text.Trim().Length <= 0 && ddListFName.Text.Trim().Length > 0)
            {
                //search for first name
                lstPerson = op.GetPersonInfoFromFirstName(ddListFName.Text);
                Debug.WriteLine("Search on first name end : " + DateTime.Now.ToString());
            }
            else if (ddListFName.Text.Trim().Length > 0 && ddListLName.Text.Trim().Length > 0)
            {
                //search for first & last name
                lstPerson = op.GetPersonInfoFromFirstAndLastName(ddListFName.Text, ddListLName.Text);
                Debug.WriteLine("Search on first & last name end : " + DateTime.Now.ToString());
            }
            else
            {
                //both are null, so no seach is done on DB
                lstPerson = null;
            }
            if (lstPerson != null)
                foreach (Person p in lstPerson)
                {
                    txtID.Text = p.Id.ToString();
                    txtFName.Text = p.FirstName;
                    txtLName.Text = p.LastName;
                    txtRoleId.Text = p.IDRole.ToString();
                }
        }

        private void ClearFields()
        {
            txtID.Text = "";
            txtFName.Text = "";
            txtLName.Text = "";
            txtRoleId.Text = "";
        }

        protected void btnNew_Click(object sender, EventArgs e)
        {
            ClearFields();
            Session["status"] = "NEW";
        }

        private string UpdateStatusMessage(int nStatus)
        {
            string sStatusMsg = "";
            if (nStatus == 1)
            {
                //New insert
                sStatusMsg = "Successfully inserted record";
            }
            else if (nStatus == 2)
            {
                //Update operation
                sStatusMsg = "Successfully updated record";
            }
            else if (nStatus == 3)
            {
                //delete operation
                sStatusMsg = "Successfully deleted record";
            }
            else
            {
                //get lost
                sStatusMsg = null;
            }
            return sStatusMsg;
        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            PeopleOperations op = new PeopleOperations();
            int nId = System.Convert.ToInt32(txtID.Text);
            string sFName = txtFName.Text;
            string sLName = txtLName.Text;
            int nRoleId = System.Convert.ToInt32(txtRoleId.Text);
            if(Session["Status"].Equals("NEW"))
            {
                //validate records
                //save new record               
                if (op.InsertNewPerson(nId, sFName, sLName, nRoleId) == true)
                {
                    //update message or redirect to success page
                    ClearFields();
                    lblStatus.Text = UpdateStatusMessage(1);
                    Session["Status"] = "UPDATED";
                }
                else
                {
                    Session["Status"] = "ERROR UPDATE";
                }
            }
            else if(Session["Status"].Equals("EDIT"))
            {
                //Update existing record
                if (op.UpdatePerson(nId, sFName, sLName, nRoleId) == true)
                {
                    lblStatus.Text = UpdateStatusMessage(2);
                    Session["Status"] = "UPDATED";
                    ClearFields();
                }
                else
                {
                    Session["Status"] = "ERROR UPDATE";
                }
               
            }
            else
            {
            }
           
        }

        protected void btnEdit_Click(object sender, EventArgs e)
        {
            Session["status"] = "EDIT";
        }

        protected void btnDelete_Click(object sender, EventArgs e)
        {
            if (txtID.Text.Length > 0)
            {
                int nId = System.Convert.ToInt32(txtID.Text);
                PeopleOperations op = new PeopleOperations();
                if (op.DeletePerson(nId) == true)
                {
                    lblStatus.Text = UpdateStatusMessage(3);
                }
            }
            ClearFields();
        }
    }
}

NHibernate and pre-defined queries

I have been wanting to update this BLOG for sometime now……..A lot of people have been enquiring me on my updates…..I have never felt so much pressure to release my updates as this one.

I had spent a weeks time to investigate more on LINQ and do some comparison on LINQ (vs) NHibernate which consumed some time. I’ll soon release my work on LINQ here, at the moment, it’s all NHibernate

Another way to fetch records from DB is using CreateSQLQuery, and write in your SQL query.

        public IList ListSalaryInfo()
        {        
            ISession session = NHibernateHelper.GetCurrentSession();
            ITransaction tx = session.BeginTransaction();
            IList lstSalary = session.CreateSQLQuery("SELECT * FROM SALARY").AddEntity(typeof(NHibWebApplication.Entity.Salary)).List();
            tx.Commit();
            NHibernateHelper.CloseSession();
            return lstSalary;
        }
        

Filter Options

Execute a sql query with filter option. In the example below we query Roles table to fetch records selectively. Please note that in real time scenarios, we hardly get to use “*”. Replace “*” with appropriate column names.

                public int GetRoleId(string sRole)
                {
                    Role role = null;
                    ISession session = NHibernateHelper.GetCurrentSession();
                    ITransaction tx = session.BeginTransaction();
                    role = new Role();
                    role.RoleName = sRole;
                    NHibernate.Expression.Example example = NHibernate.Expression.Example.Create(role).ExcludeProperty("Id");
                    IList lstRole = (List)session.CreateCriteria(typeof(Role)).Add(example).List();
                    if (lstRole.Count > 0)
                    {
                        role.Id = lstRole[0].Id;
                    }
                    //return role.Id;
                    return role.Id;
                }
            

Pre-Defined Queries

NHibernate allows execution of pre-defined queries. We can define queries in XML and execute it from our application. This will make our application look much cleaner. This is how we can do it

public IList ExecPreDefinedQuery()
        {
            Role role = new Role();
            ISession session = NHibernateHelper.GetCurrentSession();
            ITransaction tx = session.BeginTransaction();
            IList lstRole = (IList)session.GetNamedQuery("roles").SetInt32("roleId",1).List();
            return lstRole;
        }
        

In the example above, we have executed a query by name “roles” stored in the file Roles.xml.hbm. These queries can be stored in a common XML and referred in any hbm files. But I prefer to put across respective queries in appropriate hbm files. This would make things comfortable to edit, incase if the number of queries are less.

Stored Prcedures

Support for execution of procedure has been limited in the previous versions of Hibernate. After a lot of debate with internal community and challenges, I managed to execute it. Here’s the same of the code.

I have 2 sections of it. First, procedure without any parameters and Second, with parameter. In a similar fashion, we will be able to execute functions and cursors.

            public IList ExecProcedureWithNoParams()
            {
                Role role = new Role();
                ISession session = NHibernateHelper.GetCurrentSession();
                ITransaction tx = session.BeginTransaction();
                IList lstRole = (IList)session.GetNamedQuery("spRoles").List();
                return lstRole;
            }
        

Procedure with parameter

            public IList ExecProcedureWithParams()
            {
                ISession session = NHibernateHelper.GetCurrentSession();
                ITransaction tx = session.BeginTransaction();
                IList  lstRole = (IList)session.GetNamedQuery("spParamRoles").SetInt32(0, 2).List();
                return lstRole;
            }
        

I had defined these procedure names in my Roles.xml.hbm file. Snippet of the file is appended

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="NHibWebApplication.Entity.Role, NHibWebApplication" table="Role">
<id name="Id" column="Id" type="int">
<generator class="assigned" />
</id>
<property name="RoleName" column="Designation" type="String" length="50"/>
</class>
<sql-query name="roles">
<return alias="roles" class="NHibWebApplication.Entity.Role, NHibWebApplication"/>
SELECT Id, Designation from ROLE where Id = :roleId
</sql-query>
<sql-query name="spRoles">
<return alias="spRoles" class="NHibWebApplication.Entity.Role, NHibWebApplication"/>
Exec GetAllRoles
</sql-query>
<sql-query name="spParamRoles">
<return alias="spParamRoles" class="NHibWebApplication.Entity.Person, NHibWebApplication"/>
exec GetPersonInfo ?
</sql-query>
</hibernate-mapping>

Await more updates on filters, projection and relationship (one-to-one, one-to-many, many-to-one and many-to-many) samples