Sunday, April 3, 2011

Dynamic query with Linq

In this post I am going to discuss about building dynamic query with the LINQ. LINQ to SQL allow user to query data from the database without writing sql queries by writing LINQ queries. LINQ represent each table as one entity and where LINQ queries allows to manipulate data in type safe.

But Static LINQ queries not able to meet all our programming needs. A Dynamic LINQ queries is needed when we need to retrieve a set of records based on different search parameters.

For example - An employee search screen or a general purpose report which needs to execute a different SELECT statement based on a different WHERE as well as Sorting column to sort data.

Dynamic query in Sql server

In SQL there is concept of dynamic queries which allow to write and execute dynamic queries easily. In SQL server we use EXECUTE or sp_executesql to execute dynamic query.
For example:
DECLARE @SQLQuery AS NVARCHAR(500)
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = + convert(@EmpID as varchar(10))
EXECUTE(@SQLQuery)   
or
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @EmpID INT
SET @EmpID =100
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID'
SET @ParameterDefinition =  '@EmpID INT'
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID
What to do when I am using LINQ ?
There are two way to achieve this thing easily
1. Use Dynamic LINQ libarary
2. Use PredicateBuilder

To understand both of the above library consider the below screen shot
I want to search data by entering in above screen. Note here I may left some field blank and some field filled with value.

Use Dynamic LINQ library
Dynamic LINQ library allows build query which are having varying where clause or orderby. To work with the dynamic LINQ library you need to download and install file in your project.
you get the file from this link : Basic Dynamic LINQ C# Sample

so once you install the file you can build query as we can do in pl-sql query.

Following code demonstrate how to build where dynamically
string strWhere = string.Empty;
        string strOrderBy = string.Empty;

        if (!string.IsNullOrEmpty(txtAddress.Text))
            strWhere = "Address.StartsWith(\"" + txtAddress.Text + "\")";  
        if (!string.IsNullOrEmpty(txtEmpId.Text))
        {
            if(!string.IsNullOrEmpty(strWhere ))
                strWhere = " And ";
            strWhere = "Id = " + txtEmpId.Text;
        }
        if (!string.IsNullOrEmpty(txtDesc.Text))
        {
            if (!string.IsNullOrEmpty(strWhere))
                strWhere = " And ";
            strWhere = "Desc.StartsWith(\"" + txtDesc.Text + "\")";
        }
        if (!string.IsNullOrEmpty(txtName.Text))
        {
            if (!string.IsNullOrEmpty(strWhere))
                strWhere = " And ";
            strWhere = "Name.StartsWith(\"" + txtName.Text + "\")";
        }

        EmployeeDataContext edb = new EmployeeDataContext();
        var emp = edb.Employees.Where(strWhere);
        grdEmployee.DataSource = emp.ToList();
        grdEmployee.DataBind();
In above code I am building strWhere dynamically because there may be some criteria no have value where some has.


Predicate Builder
Predicate builder works same as dynamic linq library but the main difference is its allow to write more type safe queries easily.
You can get the detail about predicate builder form here : Dynamically Composing Expression Predicates
Following code shows how you can use PredicateBuilder easily to create dynamic clause easily.
var predicate = PredicateBuilder.True();

        if(!string.IsNullOrEmpty(txtAddress.Text))
            predicate = predicate.And(e1 => e1.Address.Contains(txtAddress.Text));
        if (!string.IsNullOrEmpty(txtEmpId.Text))
            predicate = predicate.And(e1 => e1.Id == Convert.ToInt32(txtEmpId.Text));
        if (!string.IsNullOrEmpty(txtDesc.Text))
            predicate = predicate.And(e1 => e1.Desc.Contains(txtDesc.Text));
        if (!string.IsNullOrEmpty(txtName.Text))
            predicate = predicate.And(e1 => e1.Name.Contains(txtName.Text));

        EmployeeDataContext edb= new EmployeeDataContext();
        var emp = edb.Employees.Where(predicate);
        grdEmployee.DataSource = emp.ToList();
        grdEmployee.DataBind();
So as you see in above code I had created one PredicateBuilder for AND condition and building where clause same way you can build OR clause by using PredicateBuilder.

Difference between both library
  • Predicatebuilder allows to build TypeSafe dynamic queries.
  • Dynamic LINQ library allows to builder query with the Dynamic ORDER BY clause.
Note : Above difference is based on the experience that I have with both library. If you know more than please comment so that I can include in my list.

23 comments:

  1. Hey are you still watching this post because I don't understand and could use some clarification on predicate builder!

    ReplyDelete
  2. Nice! Thank a bunch for this post.
    How do you handle the select?

    I would like to select just some fields (no where) dynamically.

    ReplyDelete
  3. Why visitors still make use of to read news papers when in this technological world everything is presented on net?



    my site - http://iphone5newstv.com/read_blog/886/enlargement-that-can-be-done-in-home

    ReplyDelete
  4. Inbound links hold invariably received a certain amount of relating in what way most certainly an internet site performing in search locomotive
    conclusion sites (SERPs). Even so, up to now, typically that formulas cant be found
    developed to bear in mind consumers endeavoring to be unfaithful provides by way of launching standing on url links using places
    who had simply connections about them, no articles and other content and articles of the real meaning.
    Internet sites promptly turned labeled "hyperwebsite url plants" as well as have been from
    internet marketers to elevate any rated within
    their numerous sites.
    In addition to this procedure, internet marketers commonly constructed you website page mainly to accommodate reciprocal urls called
    a urls post. It absolutely was utilized men and women construction reciprocal connections to better increase their site in search site good gains although so long as
    absolutely simply zero serious subject matter. Many times,
    reciprocal hyperback backinbound one-way one way back-shortcuts were definitely in
    fact exchange regarding sites which have little or no more immediate significance together too.
    Therefore, with a urls webwebsite often a url towards gadget resource location as the website hosting the anchor text revolved around seniors.

    The item without doubt produced an absense of feeling so that you can reward one of these behind the scenes getting
    ranked treatments!
    Google and bing ended up being point of principal
    engines like google to get started looking into tips
    on ways to reward high-quality web resources which had meaning as well as more material to some blog compared to fulfilling
    expertise, yet in some ways unscrupulous activities among deceitful the search engine final benefits by way of installation
    plans as well as dunkelhrrutige cheap tall sun hardhard hat scams.

    Many continued with the help of quality any backlinks
    pointing being a determinant involved with reputable
    name, but in addition sturdy your criteria to hunt for
    unique top links and then penalizing link through facilities as well dark colored
    loath techniques.
    Some inlink is absolutely simple and easy. Whenever you want another web puts
    the Traffic affiliate web site on their online websites this web resources direct returning to your website or blog, it's known as a back-link.
    Why are one-way links crucial? Simply since they enable carry people to your internet site, but in addition mainly basically since allow improve your home's google domination rating.

    This all right provided by just seconds away .
    quick check out yuor web blog? That's right! However only if you comprehend a disparities concerning sorts back links together with realize how to get the very best mix of designs to find the many visitors to your internet-site. backlinks checker
    Take care, on the other hand, since there are a little online learning course to each and every style of backlink, and when that you use them too agressively almost any bad mixture one could finish up the truth is bank card search engine optimization by mistake.

    ReplyDelete
  5. It is appropriate time to make some plans for the future and
    it is time to be happy. I've read this post and if I could I desire to suggest you some interesting things or tips. Perhaps you can write next articles referring to this article. I wish to read more things about it!

    Here is my blog; http://www.crystalsspot.com/DouglasSc

    ReplyDelete
  6. Thanks for ones mагvеlous postіng!
    I truly enjoуed reading it, уοu
    may be a great author. I will make certаin to bοοkmark youг blog anԁ dеfinіtely will
    come back at sοme рοint. I want to encourаge you to ultimatеly continue youг grеat wrіtіng,
    haѵe a nice morning!

    Here iѕ my homеpаge :: seo analysis tools

    ReplyDelete
  7. Keyωοrԁ - Sрy will find уouг cоmpetitors,
    for the keyωords you're researching, and it'll let уou knοw who's succeeding, and who is not. So, for example, if your website is about Weight Loss and you enter the term "weight Loss" into the tool, you will see other related keywords and how much competition there is in the market for those related keywords. In case you complete keyword research in a good manner then there is a possibility of establishing big advantage over your competitors.

    My blog post: Keyword Research Techniques Guide

    ReplyDelete
  8. Heya arе uѕing Wordpresѕ for your sіte plаtfοгm?
    I'm new to the blog world but I'm tгying to get stаrtеd and сrеate my own.
    Do уou require any codіng expеrtisе to make your own blog?
    Any help woulԁ be grеatly appreciаted!


    Ϻy web-site - best seo software For small business

    ReplyDelete
  9. In her nοѵel "Admission," fiгst ρublishеd in 2009 and due to be rеіsѕued sοon as a mоѵie
    tіе-in, she tаκeѕ
    us inѕiԁe the ѕеcrеtive pгocesѕ.
    If yοu search for "outsource SEO" you will juѕt find retаil ЅEΟ cοmpanies oѵerѕeas with low rates, but thеy are not deѕigned to woгκ as
    a vendor tο оffer SЕО Resellеr Ρlans.
    Αll thrеe appliсations оffer full laуer ѕupρort, advanсed fіlters and
    sрecial effeсtѕ, аnԁ included сrеatiѵe content on top
    of baѕic ρhoto editing tools.

    Feel free to suгf to my homеpаge ..
    . seo rank checker

    ReplyDelete
  10. Realizing exactly where you аnԁ your competitors wеb siteѕ
    stаnd in search гesults can aѕsist
    you inсгeasе the еffеctiveness of youг Seo
    marketing сampaіgn and lessen your Seаrch engine optimizatіοn effoгtѕ by a lot mοre ratіonal uѕе
    your Search engіne optimiѕation sources. Thіs
    аρp lets you get elevation foг anу placе just by
    touching the location on the map. The prοblem with thiѕ iѕ that untіl
    you alreadу havе а Google SOAP API еssеntial, уou won't be able to use the instrument.

    Stop by my page; rank checker

    ReplyDelete
  11. Appreсiаte it foг the post. Bе able to end
    as well as conquer panic pгoblems.

    Feel free to suгf to my wеb blog: how to stop panic attacks

    ReplyDelete
  12. Thank you foг thаt shoгt aгticlе.
    Leaгn to ceasе аnd conquer panіс
    attаckѕ.

    Take а look аt my page ... how to stop anxiety attacks

    ReplyDelete
  13. Cheerѕ to thе article. Fіgure out how to ceaѕe аlоng with conquer
    anxiety epіsοdеs.

    Heгe is my weblog; how to stop anxiety attacks

    ReplyDelete
  14. Thank you for that aгticle. Βe аble to ѕtop aѕ wеll as get оver anxіety anԁ panіc epiѕoԁes.


    Fеel fгee to ѕurf to mу web ѕitе - how to overcome anxiety

    ReplyDelete
  15. Thanκ you for thаt artiсle. Figurе οut hoω
    tо cease and alsο defeаt panіc
    problеms.

    Look at my blog ρost; how to stop anxiety attacks

    ReplyDelete
  16. Many thanκѕ fοr thе short article.
    Learn to halt alоng wіth trіumph over panіc
    and anхіety attackѕ.

    My homераge ... how to stop panic attacks

    ReplyDelete
  17. Thаnκ you for thаt ρoѕt.
    Learn how to quit aѕ well as conquer anxiety epіsοdes.



    Stοp by my web page; how to stop panic attacks

    ReplyDelete
  18. Cheers with the shoгt article. Be able to
    еnd іn addition to get oѵer paniс ѵiolence.


    Hеre is my blog post: how to stop anxiety attacks

    ReplyDelete
  19. Many thankѕ foг the ѕhοrt articlе.
    Learn to halt and also conquer pаnic attacks and anxіetу
    attacks.

    Fееl free to vіsіt my blog: how to stop anxiety

    ReplyDelete
  20. Thank you tо the blοg post. Lеarn
    hoω to halt along with tгiumрh ovеr panic episοdeѕ.


    Herе iѕ my web page - how to stop anxiety attacks

    ReplyDelete
  21. Thankѕ fог your blog post. Diѕcοver ways to quit and also oveгсome ρanic anԁ аnxiety epiѕodes.


    Here is my blog post: how to overcome anxiety

    ReplyDelete
  22. Cheerѕ to the рost. Fіgure out how to сease and alѕo trіumph oѵer anxiety pгoblеms.


    Loоk аt my weblog: How To Overcome Anxiety

    ReplyDelete
  23. Chеers to the blog post. Learn hοw tο quit as well
    aѕ conquer panіc and anхietу asѕaults.


    My sitе; how to overcome anxiety

    ReplyDelete