Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How do I use Database.SqlQuery<no class>  without a known class?

Posted on 2014-07-29
7
4,957 Views
Last Modified: 2014-08-13
In this Stored Procedure call:
string color = "Red";
SqlParameter colorParam = new SqlParameter(@color, color);
var redTractors = context.Database.SqlQuery<Tractor>("usp_GetTractors", @color, colorParam).ToList();
everything works fine because you know the results are of the Tractor class,
BUT,
what if you don't know the return type/class of the stored procedure, like in the case of a PIVOT table where the column names in the result are not known ahead of time?
string propertyId = 62;
SqlParameter propIdParam = new SqlParameter(@propertyId, propertyId);
var summary = context.Database.SqlQuery<?????>("usp_GetSummaryData", @propertyId, propIdParam).ToList();
0
Comment
Question by:jamestieman
  • 4
  • 3
7 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40226856
Hi jamestieman;

Create a class which will hold a single row from the result set. The names of the properties of the class must match the columns names, spelling and capitalization, of the results from the query. In the sample blow I created a class called SummaryResults with three public properties. These properties match what is expected from the query. That should give you what you need.
string propertyId = "62";
SqlParameter propIdParam = new SqlParameter("@propertyId", propertyId);
var summary = context.Database.SqlQuery<SummaryResults>("usp_GetSummaryData @propertyId", new object[] {propIdParam}).ToList();

public class SummaryResults
{
	public string Name { get; set; }
	public int Total { get; set; }
	public string Department { get; set; }
}

Open in new window

0
 

Author Comment

by:jamestieman
ID: 40227263
The problem is that the PIVOT is on the Description column and descriptions can be added, edited and deleted.  Also, different properties may have different descriptions.  Some properties will have up to 200 different Account Descriptions.  The first part of the PIVOT shows how I get all the Descriptions necessary for the PIVOT clause.
 
The only thing that makes sense to me is to query for the all the different descriptions, like the first part of the PIVOT, create a dynamic class on the fly, then call the stored procedure using the created class.  
My ultimate objective is to create a CSV file out of the data and email it to specific users.
If I could have SQL Server return a CSV file, then I wouldn't need the class at all.

Here is the PIVOT:
CREATE PROCEDURE [dbo].[usp_DynamicSummaryPivot]
      -- Add the parameters for the stored procedure here
      @masterPropertyId int = 60,
      @propertyId int = 62,
      @year int = 2014,
      @month int = 6
AS
BEGIN

DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
SET @columns = N'';

SELECT @columns += N', p.' + QUOTENAME(Description)
      FROM (SELECT mdl.Description
FROM         TransactionHeaders th INNER JOIN
                      TransactionDetails td ON th.Id = td.TransactionHeaderId INNER JOIN
                      PropertyDataCollectors pdc ON td.PropertyDataCollectorId = pdc.Id INNER JOIN
                      MasterDataCollectors mdc ON pdc.MasterDataCollectorId = mdc.Id INNER JOIN
                      MasterDetailLines mdl ON mdc.MasterDetailLineId = mdl.Id
WHERE     (th.PropertyId = @propertyId) AND (mdl.MasterPropertyId = @masterPropertyId)
Group by mdl.Description) AS x;

SET @sql = N'
SELECT [Property], [Date], ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT Properties.Name as Property, CONVERT(VARCHAR(10), [TransactionHeaders].[TransactionDate], 101) as [Date], MasterDetailLines.Description AS MDLDesc,
                      ISNULL(TransactionDetails.Amount, 0) AS Total
FROM         TransactionHeaders INNER JOIN
                      Properties ON TransactionHeaders.PropertyId = Properties.Id INNER JOIN
                      TransactionDetails ON TransactionHeaders.Id = TransactionDetails.TransactionHeaderId INNER JOIN
                      PropertyDataCollectors ON TransactionDetails.PropertyDataCollectorId = PropertyDataCollectors.Id INNER JOIN
                      MasterDataCollectors ON PropertyDataCollectors.MasterDataCollectorId = MasterDataCollectors.Id INNER JOIN
                      MasterDetailLines ON MasterDataCollectors.MasterDetailLineId = MasterDetailLines.Id
WHERE TransactionHeaders.PropertyId = ' + STR(@propertyId) + '
AND YEAR(TransactionHeaders.TransactionDate) = ' + STR(@year) + '
 AND MONTH(TransactionHeaders.TransactionDate) = ' + STR(@month) + '
                      ) as t
PIVOT
(
  SUM(Total) FOR MDLDesc IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
--PRINT @propertyId;
--PRINT @sql;
EXEC sp_executesql @sql;
END


GO
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40227284
Are you saying that the stored procedure is not always or will always return the same columns from the database?
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:jamestieman
ID: 40227315
It will probably never return the same columns because the description of different accounts will be different between properties.  Also, some properties will have more accounts than others.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40227425
Please try the following. Open the EDMX designer and in the Model Browser window expand the Complex Type node. See if you have a complex type called something like usp_GetSummaryData_Result. Use that as the type of entities to be returned, something like the following:
var summary = context.Database.SqlQuery<usp_GetSummaryData_Result>("usp_GetSummaryData @propertyId", new object[] {propIdParam}).ToList();

Open in new window

EF should have analyzed the store procedure and determined the result set.
0
 

Author Comment

by:jamestieman
ID: 40227524
There's nothing in the Complex Types, so I right-clicked the SP and clicked Add Function Import.  When I click Get Column Information, I get "The selected stored procedure returns no columns.".
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40227784
Well then you are not going to be able to execute that stored procedure using Entity Framework because all the methods that allow you to do that requires a known type to be return. You will need to use ADO .Net  and possibly use a data reader to return the values and build your CSV file from the reader.
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Convert Select to DropDownListFor MVC 5 2 30
ASP.NET (VB) return a record 2 37
ASP.NET Content Page 3 27
Amazon S3 .Net error 5 17
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question