LINQ to SQL Efficiency

Question

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

Answer 1

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

Answer 2

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

            }                
        }
    }