Home > DeveloperSection > Forums > LINQ to SQL Efficiency
Alex Leblois
Alex Leblois

Total Post:67

Points:471
Posted on    December-08-2014 10:35 PM

 ASP.Net LINQ  SQL Server 
Ratings:


 2 Reply(s)
 524  View(s)
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();

}



ben reitman
ben reitman

Total Post:96

Points:676
Posted on    December-09-2014 6:07 AM

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


Royce Roy
Royce Roy

Total Post:134

Points:938
Posted on    December-09-2014 7:14 AM

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();

 

            }               

        }

    }


Don't want to miss updates? Please click the below button!

Follow MindStick