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