实体框架自定义SQL查询返回泛型类型 [英] Entity Framework Custom SQL Query returning generic type

问题描述

我正在尝试制作一个通用的报告系统，以便我可以在报告中添加报告给我的程序，而不是每次在报表中添加新版本的程序。

目前，我有部分工作;

我的自定义报告SQL存储在表中以及报告名称。报告名称用于按钮，用户点击按钮时，我希望SQL执行并绑定到gridview，所以尽可能的通用。

这似乎是麻烦来的地方。获取SQL并执行它是好的，但绑定到gridview似乎是不可能的，我只收到一列，名为Column，显示1行的值为System.Object。

$ b $我认为问题是因为我从多个表中返回数据，例如

  SELECT c.CertificateName，e .EntityName，
 ce.CertificateDate，ce.CertificateExpiry，ce.CertificateNumber 
 FROM FCERTSTest.dbo.CertificateEntries ce 
 INNER JOIN FCERTSTest.dbo.Certificates c 
 ON c.CertificateID = ce.Certificate_ID 
 INNER JOIN FCERTSTest.dbo.Entities e 
 ON e.EntityID = ce.Entity_ID 
 WHERE FirstNotificationSent ='1'
  

我正在使用

  using（DBContainer db = new DBContainer（））
 {
 grid.DataSource = db.Database.SqlQuery< dynamic>（SQLCommand）.ToList（）; 
} 
  

我已经尝试替换 dynamic 与对象并得到相同的问题...
我明白，最好的解决方案是有一个自定义类，并替换动态与该自定义类，但随后我失去了它的通用性的好处，因为不同的报告可能不会返回相同的结果，我需要一个新的自定义类...

解决方案

使用 SqlDataAdapter 和 DataSet 。 EF不适用于您没有特殊类型的结果集的动态查询。

I'm trying to make a generic reporting system so that I can add reports to my program on the fly, rather than issuing a new version of the program every time I have to add in a report.
Currently, I have it partly working;
My custom report SQL is stored in a table, along with the report name. The report name is used for the buttons for the user to click, when they click the button, I want the SQL to execute and be bound to a gridview, so it's as generic as possible.

This seems to be where the troubles come though. Getting the SQL and executing it is fine, but binding it to a gridview seems impossible, I only get one column, called Column, that displays 1 row with a value of System.Object.

I think the problem is because I'm returning data from multiple tables, i.e.

SELECT c.CertificateName, e.EntityName, 
       ce.CertificateDate, ce.CertificateExpiry, ce.CertificateNumber 
FROM FCERTSTest.dbo.CertificateEntries ce 
INNER JOIN FCERTSTest.dbo.Certificates c 
ON c.CertificateID = ce.Certificate_ID 
INNER JOIN FCERTSTest.dbo.Entities e 
ON e.EntityID = ce.Entity_ID 
WHERE FirstNotificationSent = '1'

I'm currently using

using (DBContainer db = new DBContainer())
{
    grid.DataSource = db.Database.SqlQuery<dynamic>(SQLCommand).ToList();
}

I've tried replacing dynamic with object and get the same problem... I understand that the best solution would be to have a custom class and replace dynamic with that custom class, but then I lose the benefits of it being generic as a different report may not return the same results and I would need a new custom class...

解决方案

Use SqlDataAdapter and DataSet. EF doesn't work with "dynamic queries" where you don't have special type for result set.

这篇关于实体框架自定义SQL查询返回泛型类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！