Search and selection of data using LINQ


In our programming practice we often face a situation, when we should search and select some data according to parameters, which are defined by users. In this case we, as usual, have some form (screen) with special fields, which allow to a user to select parameters for his own choice. For example, we have some form to view data of a table (named Persons) with fields ID, FirstName, LastName, (some other fields), HomeSite. Our users want to find in one case only person with HomeSite London1 and FirstName Mike; in another case a user want to see all persons, that there are in our table. In this article I will show how you can use LINQ for such situations.

For simplicity we will consider a little project with only two tables: C_Person and NC_Site (fig.1) :

01.GIF

Fig. 1.

In our solution (named So) there are three projects: project with all our web forms etc. (So), project for our business logic (SoBL, output type class library) and project for data access layers (SoDal, output type class library). In SoDal project there is dbml file (LINQ to SQL class), named SoDC.dbml, with our tables (fig. 2):

02.GIF

Fig. 2.

Our WebFormPerson (in So project) has four field to enter parameters, a GridView and a button (now we are interested only in selection of some data and not in inserting etc.) and looks like this (fig. 3):

03.GIF

Fig.3.

Of course, without LINQ (!) we should use a stored procedure with the Case function (where, if some parameter equals, for example, -999, we just don't do any select for this parameter) like to following:

ALTER PROCEDURE dbo.usp_Persons

      @PersonID nchar(10) = '-999',

      @FirstName nchar(20) = '-999',

      @LastName nchar(20) = '-999',

      @Site smallint = -999

AS

      select

            PersonNum,

            PersonId,

            FirstName,

            LastName,

            Email,

            Telephone as Telephon,

            Note,

            SiteHome,

            b.SiteName as SiteName,

            a.LastUpdate as LastUpdate

      from  dbo.C_Person a

                  LEFT OUTER join NC_Site b

                  ON a.SiteHome = b.SiteNum

      where

            PersonID =

            case

                  when  @PersonID = '-999' then

                        PersonID

                  else  ltrim(rtrim(@PersonID))

            end

            and

            FirstName =

            case

                  when  @FirstName = '-999' then

                        FirstName

                  else  ltrim(rtrim(@FirstName))

            end

            and

            LastName =

            case

                  when  @LastName = '-999' then

                        LastName

                  else  ltrim(rtrim(@LastName))

            end

            and

            SiteHome =

            case

                  when  @Site = -999 then
                        SiteHome

                  else  (@Site)
            end


Then (without LINQ !) we should call our stored procedure in some PersonDal class of SoDal project and use some DataObjectMethod in, for example, PersonBL class of SoBL project, where we can make some changing for business logic (for example: if FirstName = M, then site=3). But, in fact, main part of our business logic just hide in stored procedure.

Of course, we can use a stored procedure with LINQ to SQL class and just add our stored procedure to SoDC.dbml (fig. 4):

04.GIF

Fig 4.

Then we just call it in our PersonBL class of SoBL project (as method of DataContext):
 

SoDCDataContext dc = new SoDCDataContext();

var persons = (dc.usp_Persons(personId, firstName, lastName,               site)).ToList();

But it is better to see all our logic in the PersonBL class (here really all our business logic has to be!) and use stored procedure in, may be, very special and complicated cases. So, we just change our stored procedure to some LINQ code. Suppose, we use some special class to see persons selected data:

public class C_PersonDGV

    {

        public short PersonNum { get; set; }

        public string PersonID { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

        public string Email { get; set; }

        public string Telephon { get; set; }

        public string Note { get; set; }

        public short? SiteHome { get; set; }

        public string SiteName { get; set; }

        public DateTime? LastUpdate { get; set; }

    }

Our DataObjectMethod of the PersonBL class (in SoBL project) will look so:
 

[DataObjectMethod(DataObjectMethodType.Select, true)]

public static List<C_PersonDGV> FindPersonsDGV(

    string personId, string firstName,

    string lastName, short site)

{

    SoDCDataContext dc = new SoDCDataContext();

    IEnumerable<C_PersonDGV> personsDGV =

        from p in dc.C_Persons

        where

        p.PersonID.Equals((personId.Equals("-999")) ?

            p.PersonID : personId)

        &&

        p.FirstName.Equals((firstName.Equals("-999")) ?

            p.FirstName : firstName)

        &&

        p.LastName.Equals((lastName.Equals("-999")) ?

            p.LastName : lastName)

        &&

        p.SiteHome.Equals(site == -999 ?

            p.SiteHome : site)

        select new C_PersonDGV

        {

            PersonNum = p.PersonNum,

            PersonID = p.PersonID,

            FirstName = p.FirstName,

            LastName = p.LastName,

            Email = p.Email,

            Telephon = p.Telephone,

            Note = p.Note,

            SiteHome = p.SiteHome,

            SiteName = p.NC_Site.SiteName,

            LastUpdate = p.LastUpdate

        };

    ////------or lamda----------

    //IEnumerable<C_PersonDGV> personsDGV = dc.C_Persons.Where

    //    (

    //        p =>

    //        p.PersonID.Equals((personId.Equals("-999")) ?

    //            p.PersonID : personId)

    //        &&

    //        p.FirstName.Equals((firstName.Equals("-999")) ?

    //            p.FirstName : firstName)

    //        &&

    //        p.LastName.Equals((lastName.Equals("-999")) ?

    //            p.LastName : lastName)

    //        &&

    //        p.SiteHome.Equals(site == -999 ?

    //            p.SiteHome : site)

    //    ).Select(p => new C_PersonDGV

    //        {

    //            PersonNum = p.PersonNum,

    //            PersonID = p.PersonID,

    //            FirstName = p.FirstName,

    //            LastName = p.LastName,

    //            Email = p.Email,

    //            Telephon = p.Telephone,

    //            Note = p.Note,

    //            SiteHome = p.SiteHome,

    //            SiteName = p.NC_Site.SiteName,

    //            LastUpdate = p.LastUpdate

    //        }

    //    );

    ////---------------------

    return personsDGV.ToList();

}
 

Now we are ready to return to the WebFormPerson in our So project and configure (of course, using the method FindPersonDGV) ObjectDataSource of our GridView (fig. 5, fig. 6):

05.GIF

Fig. 5.

06.GIF

Fig. 6.

At last, we bind our GridView on click event:
 

protected void ButtonFind_Click(object sender, EventArgs e)

{

    if (GridViewGeneral.DataSource != null )

    {

        GridViewGeneral.DataBind();

    }

}


and if we, for example, select site London1 and click Find button we get the following (fig. 7):

07.GIF

Fig. 7.

Good luck in programming !

Up Next
    Ebook Download
    View all
    Learn
    View all