Generic approach to dealing with multiple result sets from EF stored procedure

Question

EF 6, .NET 4.51

I am trying to build a generic helper class that will help me "translate" each of the result sets into a type safe class as described here Handle multiple result from a stored procedure with SqlQuery

For my solution I want to pass the following to my helper class (MultiResultsetsHelper):

Generic Return Type
ObjectContext
DataReader
List of class types in order of the result sets coming back

and then have the helper class do the heavy lifting of populating 1. Below is the code so far:

Classes For Result

public class Set1ReturnDto
{
    public int CruiseCount { get; set; }
    public string DisplayText { get; set; }
    public int DisplayValue { get; set; }
}

public class Set2ReturnDto
{
    public string DepartingFrom { get; set; }
    public string Port_Code { get; set; }
}

public class DummyReturnDto
{
    public DummyReturnDto()
    {
        Set1 = new List<Set1ReturnDto>();
        Set2 = new List<Set2ReturnDto>();
    }

    public List<Set1ReturnDto> Set1 { get; set; }
    public List<Set2ReturnDto> Set2 { get; set; }
}

Low Level Database Call

    public static DummyReturnDto DonoUspGetSideBarList(DbContext aDbContext, out int aProcResult)
    {
        SqlParameter procResultParam = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };

        DbCommand dbCommand = aDbContext.Database.Connection.CreateCommand();
        dbCommand.Parameters.Add(procResultParam);
        dbCommand.CommandText = "EXEC @procResult = [dbo].[usp_GetSideBarList] ";
        dbCommand.Transaction = aDbContext.Database.CurrentTransaction.UnderlyingTransaction;

        DbDataReader reader = dbCommand.ExecuteReader();
        aProcResult = -1;

        // Drop down to the wrapped `ObjectContext` to get access to the `Translate` method
        ObjectContext objectContext = ((IObjectContextAdapter)aDbContext).ObjectContext;

        List<Type> containedDtos = new List<Type>
                                   {
                                       typeof (List<Set1ReturnDto>), 
                                       typeof (List<Set1ReturnDto>)
                                   };

        return MultiResultsetsHelper.Process<DummyReturnDto>(reader, objectContext, containedDtos);
    }

The resulting datasets returned are:

enter image description here

Helper Class

public static class MultiResultsetsHelper
{
    /// <summary>
    ///     Given a data reader that contains multiple result sets, use the supplied object context to serialise the
    ///     rows of data in the result set into our property.
    /// </summary>
    /// <typeparam name="T">Type of the containing object that contains all the various result sets.</typeparam>
    /// <param name="aDbReader">Database reader that contains all the result sets returned from the database.</param>
    /// <param name="aObjectContext">Data context associated with the data reader.</param>
    /// <param name="aContainedDataSetReturnedTypes">
    ///     List of types in order of which the result sets are contained within the
    ///     data reader. We will serilize sequentially each result set the data reader contains
    /// </param>
    /// <returns>Retuns an object representing all the result sets returned by the data reader.</returns>
    public static T Process<T>(DbDataReader aDbReader, ObjectContext aObjectContext, List<Type> aContainedDataSetReturnedTypes) where T : new()
    {
        //What we will be returning
        T result = new T();

        for (int datasetNdx = 0; datasetNdx < aContainedDataSetReturnedTypes.Count; datasetNdx++)
        {
            //Advance the reader if we are not looking at the first dataset
            if (datasetNdx != 0)
                aDbReader.NextResult();

            //Get the property we are going to be updating based on the type of the class we will be filling
            PropertyInfo propertyInfo = typeof (T).GetProperties().Single(p => p.PropertyType == aContainedDataSetReturnedTypes[datasetNdx]);

            //Now get the object context to deserialize what is in the resultset into our type
            var valueForProperty = aObjectContext.Translate <aContainedDataSetReturnedTypes[datasetNdx]> (aDbReader);

            //Finally we update the property with the type safe information
            propertyInfo.SetValue(result, valueForProperty, null);
        }
        return result;
    }
}

However currently I cannot get this to compile.

Error 2 Operator '<' cannot be applied to operands of type 'method group' and 'System.Type'

Can someone help out? Ultimately it has to do with how we use reflection and the passed in aContainedDataSetReturnedTypes. I am happy to change things around as long as it is still easy to call MultiResultsetsHelper.Process<>()

what entity type does `EXEC @procResult = [dbo].[usp_GetSideBarList]` returns? — Oluwafemi, Jul 14 '15 at 06:49
It returns two datasets. The first one has fields as per Set1ReturnDto and the second one has fields as per Set2ReturnDto — TheEdge, Jul 14 '15 at 10:09
I would combine the two `POCO` classes together to create another class that represents the result set, then use `context.Database.SqlQuery("stored proc")` then use `Select` on that to project out into the separate `POCO` classes — Callum Linington, Jul 15 '15 at 07:20
Unless, you can fire off the `sproc` twice to give the two different results sets, then I would just fire `SqlQuery` strongly typed with the two `POCO` classes — Callum Linington, Jul 15 '15 at 07:22
@CallumLinington Isnt JoinedPocoClass actually my class DummyReturnDto? — TheEdge, Jul 15 '15 at 10:51

score 0 · Answer 1 · edited May 23 '17 at 11:58

0

This code:

aObjectContext.Translate<aContainedDataSetReturnedTypes[datasetNdx]>

will not work, because generic type parameters are always resolved at compile-time. You can't pass a Type instance at runtime.

You can still call the generic method, but you'll have to use reflection.

edited May 23 '17 at 11:58

Community

1
1

answered Jul 15 '15 at 07:14

Blorgbeard

101,031
48
228
272

OK so using reflection as MethodInfo method = typeof(ObjectContext).GetMethod("Translate"); I now get the exception System.Reflection.AmbiguousMatchException. So how do I target Translate? – TheEdge Jul 20 '15 at 06:36
There are two overloads called `Translate` - see [msdn](https://msdn.microsoft.com/en-us/library/system.data.entity.core.objects.objectcontext.aspx) - so you will have to pass the signature of the one you want to `GetMethod` using [this overload](https://msdn.microsoft.com/en-us/library/6hy0h0z1.aspx) – Blorgbeard Jul 20 '15 at 07:00

score 0 · Answer 2 · answered Jul 24 '15 at 02:45

With the help of all of the above I came up with the following (which can still be improved):

public static class MultiResultsetsHelper
    {
        /// <summary>
        ///     Given a data reader that contains multiple result sets, use the supplied object context to serialise the
        ///     rows of data in the result set into our property.
        /// </summary>
        /// <typeparam name="T">Type of the containing object that contains all the various result sets.</typeparam>
        /// <param name="aDbReader">Database reader that contains all the result sets returned from the database.</param>
        /// <param name="aDbContext">Data context associated with the data reader.</param>
        /// <param name="aDataSetTypes">Type for each type to use when we call Translate() on the current result in the data reader.</param>
        /// <param name="aContainedDataSetReturnedTypes">
        ///     List of types in order of which the result sets are contained within the
        ///     data reader. We will serilize sequentially each result set the data reader contains
        /// </param>
        /// <returns>Retuns an object representing all the result sets returned by the data reader.</returns>
        public static T Process<T>(DbDataReader aDbReader, DbContext aDbContext, List<Type> aDataSetTypes, List<Type> aContainedDataSetReturnedTypes) where T : new()
        {
            //What we will be returning
            T result = new T();

            // Drop down to the wrapped `ObjectContext` to get access to the `Translate` method
            ObjectContext objectContext = ((IObjectContextAdapter) aDbContext).ObjectContext;

            //Iterate the passed in dataset types as they are in the same order as what the reader contains    
            for (int datasetNdx = 0; datasetNdx < aContainedDataSetReturnedTypes.Count; datasetNdx++)
            {
                //Advance the reader if we are not looking at the first dataset
                if (datasetNdx != 0)
                    aDbReader.NextResult();

                //Get the property we are going to be updating based on the type of the class we will be filling
                PropertyInfo propertyInfo = typeof (T).GetProperties().Single(p => p.PropertyType == aContainedDataSetReturnedTypes[datasetNdx]);

                //Now get the object context to deserialize what is in the resultset into our type
                MethodInfo method = GetTranslateOverload(typeof (ObjectContext));
                MethodInfo generic = method.MakeGenericMethod(aDataSetTypes[datasetNdx]);

                //Invoke the generic method which we hvae constructed for Translate
                object valueForProperty = generic.Invoke(objectContext, new object[] {aDbReader});

                //Finally we update the property with the type safe information
                propertyInfo.SetValue(result, valueForProperty);
            }
            return result;
        }

        /// <summary>
        ///     Internal helper method to get the necessary translate overload we need:
        ///     ObjectContext.Translate<T>(DbReader)
        /// </summary>
        /// <param name="aType">ObjectContext.GetType()</param>
        /// <returns>Returns the method we require, null on error.</returns>
        private static MethodInfo GetTranslateOverload(Type aType)
        {
            MethodInfo myMethod = aType
                .GetMethods()
                .Where(m => m.Name == "Translate")
                .Select(m => new
                             {
                                 Method = m,
                                 Params = m.GetParameters(),
                                 Args = m.GetGenericArguments()
                             })
                .Where(x => x.Params.Length == 1
                            && x.Args.Length == 1
                            && x.Params[0].ParameterType == typeof (DbDataReader)
                //            && x.Params[0].ParameterType == x.Args[0]
                )
                .Select(x => x.Method)
                .First();
            return myMethod;
        }
    }

So assuming you have:

public class UspGetSideBarListReturnDto
{
    public List<Set1ReturnDto> Dummy1 { get; set; }
    public List<Set2ReturnDto> Dummy2 { get; set; }
}

public class Set1ReturnDto
{
    public Int32 CruiseCount { get; set; }
    public string DisplayText { get; set; }
    public Int64 DisplayValue { get; set; }
}

public class Set2ReturnDto
{
    public string DepartingFrom { get; set; }
    public string Port_Code { get; set; }
}

You can call it as:

DbDataReader reader = dbCommand.ExecuteReader();
return MultiResultsHelper.Process<UspGetSideBarListReturnDto>(reader, myDbContext, new List<Type>{typeof(Set1ReturnDto), typeof(Set2ReturnDto)}, new List<Type>{typeof(List<Set1ReturnDto>), typeof(List<Set2ReturnDto>});

The order of the aDataSetTypes needs to correspond to the list of result sets in the aDbReader.

Improvements would be:

Only pass the list of dataset types. (And have the List properties automatically determined)

Generic approach to dealing with multiple result sets from EF stored procedure

2 Answers2

Linked