CODEWITHSUNDEEP
sql-serverasp.net-mvcentity-framework
RiZe
RiZe

Reputation: 357

EF stored procedure with dynamic number of columns in result set

At first I should note that I'm new in ASP.NET (however, I have some experience with C#) and Entity Framework. I work on a school project and there is quite complicated database containing energy consumption data. Those I need (I import, E export, reactive power C and L) are stored in one column as binary compressed so to get them out I have to use a stored procedure which calls some methods in custom assembly to decompress the column and restore the data.

The stored procedure has 4 arguments:

@identify int,
@startTime datetime,
@endTime datetime,
@args nvarchar(60) -- "Selector"

The selector is rather special, it's an argument where you specify what you want in the result set, e.g. 'i' for Import column only, 'i,e' for Import and Export. They designed it like this because it's faster if you need just one column rather then all columns (because of the way the compressed data are stored).

So, I've created an ADO.NET Entity Model called EnergyConsumptionDBModel, imported the stored procedure and created complex type EnergyConsumptionResult for the stored procedure return type as follows:

public partial class EnergyConsumptionResult
{
    public System.DateTime Time { get; set; }
    public double I { get; set; }
    public double E { get; set; }
    public double L { get; set; }
    public double C { get; set; }
}

The column Time is always present in the result set, but the rest depends on the @args argument of the stored procedure. For example if I pick 'i,e,c,l' as an argument, it will return columns Time, I, E, C, L and everything is just fine but if I pick for example 'i' it returns Time, I which gives me an exception:

The data reader is incompatible with the specified 'EnergyConsumptionDBModel.EnergyConsumptionResult'.
A member of the type, 'E', does not have a corresponding column in the data reader with the same name.

So the question is, is there some simple way to solve this? Some kind of dynamic result mapping on complex type or is it much more simple to tell my colleague who designed the stored procedure to make it return all columns whatever is in @args but leave the un-used columns empty which is a solution my project leader may not like. Thanks for any help.

Upvotes: 0

Views: 2294

Answers (1)

Pawel
Pawel

Reputation: 31610

I don't think it is possible to automatically map resultsets with variable number of columns in EF. You specify the mapping at design time and EF relies on that. What you could do would be returning all columns but set the columns for which you don't return data to null. You may try using executing the stored procedure directly and use Translate method on ObjectContext (http://msdn.microsoft.com/en-us/library/dd466384.aspx) for materialization but I think this method will also expect columns in your reader that correspond to property names.

Upvotes: 2

Related Questions