LINQ TO SQL EFFICIENCY

Alex Leblois

Total Post:67

Points:471
Posted by  Alex Leblois
 828  View(s)
Ratings:
Rate this:

I am new to LINQ and I have a question regarding a "Hit the database once" type of transaction.

 

In the below code I am databinding the results of a query to a radio list. I want to run the query once, then work with the results before databinding. IE: If there are values, databind to the Radio list, otherwise show a textbox stating there are no values.

 

From my online searches I have only found that I can run the query once with a .count(), then run it again if the .count() is > 0.

 

I would prefer to hit the database once, then count the records, and proceed using the same resultset.

 

I was not sure of the terminology to use when searching, so please respond with the approprate terminology to use so that I can find the answer on my own!

 

using (RTOExceptionDataContext thisDataContext = new RTOExceptionDataContext())

{

    rdoSelectTransition.DataSource =    from tracking in thisDataContext.vw_RTOExceptionWorkflowTransitionMaps

                                                where tracking.RTOExceptionId.Equals(Convert.ToInt32(Request.QueryString["RTOExceptionId"])) &&

                                                tracking.RTOSecurityLevel.Equals((int)Master.thisUserSecurityLevel)

                                                select new { tracking.RTOTransitionCd, tracking.TransitionDisp };

    rdoSelectTransition.DataTextField = "TransitionDisp";

    rdoSelectTransition.DataValueField = "RTOTransitionCd";

    rdoSelectTransition.DataBind();

}

  1. ben reitman

    Post:96

    Points:676
    Re: LINQ to SQL Efficiency

    You don't need to do this at all. Just keep your binding code exactly as it is and use the <EmptyDataTemplate> within the markup of the GridView to indicate what should be shown in the event that you bind an empty collection to the GridView.

     

    If you're binding data to a type of control that doesn't support a feature like this, then the easiest option is to simply eagerly materialize the query into a collection and then get the size of that in-memory collection.

     

    var data = (from tracking in thisDataContext.vw_RTOExceptionWorkflowTransitionMaps

        where tracking.RTOExceptionId.Equals(Convert.ToInt32(Request.QueryString["RTOExceptionId"])) &&

            tracking.RTOSecurityLevel.Equals((int)Master.thisUserSecurityLevel)

        select new { tracking.RTOTransitionCd, tracking.TransitionDisp })

        .ToList();

     

    if(data.Any())

        //databind

    else

       //do something else

  1. Royce Roy

    Post:149

    Points:1043
    Re: LINQ to SQL Efficiency

    I did find an answer to my question! I am learning more about LINQ everyday, and I really love it! This allowed me to databind if there are results. Though the "else" is not shown below, it sets the visibility of the radio button to false.

     

            int thisUserSecurityLevel = (int)Master.thisUserSecurityLevel;

            int thisUserSelectedException = Convert.ToInt32(Request.QueryString["RTOExceptionId"]);

     

            using (RTOExceptionDataContext thisDataContext = new RTOExceptionDataContext())

            {

                var query = from tracking in thisDataContext.vw_RTOExceptionWorkflowTransitionMaps

                            where tracking.RTOExceptionId.Equals(thisUserSelectedException) &&

                            tracking.RTOSecurityLevel.Equals(thisUserSecurityLevel)

                            select new { tracking.RTOTransitionCd, tracking.TransitionDisp };

     

                if (query.Count() > 0)

                {

     

                    rdoSelectTransition.DataSource = query;

                    rdoSelectTransition.DataTextField = "TransitionDisp";

                    rdoSelectTransition.DataValueField = "RTOTransitionCd";

                    rdoSelectTransition.DataBind();

     

                }               

            }

        }

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!