在MVC5 / EF应用程序中实现动态LINQ查询？
作为一个概述，我试图将Export()功能添加到我的应用程序中 - 允许用户指定某些模型字段，并且只通过查询LINQ并使用EPPlus库导出来在这些字段中导出值。 我试图在基于这个示例的MVC5 / EF代码优先应用程序中实现动态LINQ功能，但似乎错过了一些让它工作或不理解的东西。
首先，我将一个新的类文件添加到名为DynamicLibrary.cs主项目文件夹中。 当我在这里下载.zip时，我“相信”我想要的代码是Dynamic.cs文件代码，我将代码复制到我的项目中的DynamicLibrary.cs中。 这样做可以让我在我的项目中using System.Linq.Dynamic进行引用。
现在我一直在试图弄清楚如何为Dynamic LINQ设置其余部分。
在我的ExportController中的namespace InventoryTracker.Controllers {}但在public class ExportController : Controller { }我添加了基于我的INV_Assets模型中的字段的示例代码我尝试导出：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using InventoryTracker.DAL;
using OfficeOpenXml;
using InventoryTracker.Models;
using System.Linq.Dynamic;
namespace InventoryTracker.Controllers
{
public class ExportController : Controller
{
InventoryTrackerContext _db = new InventoryTrackerContext();
// GET: Export
public ActionResult Index()
{
ExportAssetsViewModel expViewMod = new ExportAssetsViewModel();
return View(expViewMod);
}
public ActionResult Export()
{
GridView gv = new GridView();
gv.DataSource = _db.INV_Assets.ToList();
gv.DataBind();
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=InventoryAssets-" + DateTime.Now + ".xls");
Response.ContentType = "application/ms-excel";
Response.Charset = "";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gv.RenderControl(htw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
return RedirectToAction("StudentDetails");
}
[HttpPost]
public ActionResult ExportUsingEPPlus(ExportAssetsViewModel model)
{
//FileInfo newExcelFile = new FileInfo(output);
ExcelPackage package = new ExcelPackage();
var ws = package.Workbook.Worksheets.Add("TestExport");
var exportFields = new List<string>();
foreach(var selectedField in model.SelectedFields)
{
// Adds selected fields to [exportFields] List<string>
exportFields.Add(model.ListOfExportFields.First(s => s.Key == selectedField).Value);
}
// Loops to insert column headings into Row 1 of Excel
for (int i = 0; i < exportFields.Count(); i++ )
{
ws.Cells[1, i + 1].Value = exportFields[i].ToString();
}
// INVALID - Need to query table INV_Assets for all values of selected fields and insert into appropriate columns.
if (exportFields.Count() > 0)
{
var exportAssets = from ia in _db.INV_Assets
select new {
ia.ip_address,
}
ws.Cells["A2"].LoadFromCollection(exportFields);
}
var memoryStream = new MemoryStream();
package.SaveAs(memoryStream);
string fileName = "Exported-InventoryAssets-" + DateTime.Now + ".xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
memoryStream.Position = 0;
return File(memoryStream, contentType, fileName);
}
}
public class DynamicColumns : INV_Assets
{
//public int Id { get; set; }
//public int Model_Id { get; set; }
public virtual INV_Models Model { get; set; }
//public int Manufacturer_Id { get; set; }
public virtual INV_Manufacturers Manufacturer { get; set; }
//public int Type_Id { get; set; }
public virtual INV_Types Type { get; set; }
//public int Location_Id { get; set; }
public virtual INV_Locations Location { get; set; }
//public int Vendor_Id { get; set; }
public virtual INV_Vendors Vendor { get; set; }
//public int Status_Id { get; set; }
public virtual INV_Statuses Status { get; set; }
public string ip_address { get; set; }
public string mac_address { get; set; }
public string note { get; set; }
public string owner { get; set; }
public decimal cost { get; set; }
public string po_number { get; set; }
public string description { get; set; }
public int invoice_number { get; set; }
public string serial_number { get; set; }
public string asset_tag_number { get; set; }
public DateTime? acquired_date { get; set; }
public DateTime? disposed_date { get; set; }
public DateTime? verified_date { get; set; }
public DateTime created_date { get; set; }
public string created_by { get; set; }
public DateTime? modified_date { get; set; }
public string modified_by { get; set; }
}
public enum EnumTasks
{
Model = 1,
Manufacturer = 2,
Type = 3,
Location = 4,
Vendor = 5,
Status = 6,
ip_address = 7,
mac_address = 8,
note = 9,
owner = 10,
cost = 11,
po_number = 12,
description = 13,
invoice_number = 14,
serial_number = 15,
asset_tag_number = 16,
acquired_date = 17,
disposed_date = 18,
verified_date = 19,
created_date = 20,
created_by = 21,
modified_date = 22,
modified_by = 23
}
public IQueryable DynamicSelectionColumns()
{
using (var db = new TrackerDataContext())
{
string fieldIds = "," + "4,5,3,2,6,17,11,12" + ",";
var taskColum = Enum.GetValues(typeof(EnumTasks)).Cast<EnumTasks>().Where(e => fieldIds.Contains("," + ((int)e).ToString() + ",")).Select(e => e.ToString().Replace("_", ""));
string select = "new ( TaskId, " + (taskColum.Count() > 0 ? string.Join(", ", taskColum) + ", " : "") + "Id )";
return db.Task.ToList().Select(t => new DynamicColumns() { Id = t.Id, TaskId = Project != null ? Project.Alias + "-" + t.Id : t.Id.ToString(), ActualTime = t.ActualTime, AssignedBy = t.AssignedBy.ToString(), AssignedDate = t.AssignedDate, AssignedTo = t.AssignedTo.ToString(), CreatedDate = t.CreatedDate, Details = t.Details, EstimatedTime = t.EstimatedTime, FileName = t.FileName, LogWork = t.LogWork, Module = t.Module != null ? t.Module.Name : "", Priority = t.Priority != null ? t.Priority.Name : "", Project = t.Project != null ? t.Project.Name : "", ResolveDate = t.ResolveDate, Status = t.Status != null ? t.Status.Name : "", Subject = t.Subject, TaskType = t.TaskType != null ? t.TaskType.Type : "", Version = t.Version != null ? t.Version.Name : "" }).ToList().AsQueryable().Select(select);
}
}
}
我不是100％确定这是建立在正确的位置。 下面的最后一个方法有5个错误：
IQueryable - Expected class, delegate, enum, interface, or struct. InventoryTrackerContext - Expected class, delegate, enum, interface, or struct. DynamicColumns() - Expected class, delegate, enum, interface, or struct. } public IQueryable DynamicSelectionColumns() - Type or namespace definition, or end-of-file expected. 关闭}用于namespace InventoryTracker.Controllers - Type or namespace definition, or end-of-file expected.
public IQueryable DynamicSelectionColumns()
{
using (var db = new InventoryTrackerContext())
{
string fieldIds = "," + "4,5,3,2,6,17,11,12" + ",";
var taskColum = Enum.GetValues(typeof(EnumTasks)).Cast<EnumTasks>().Where(e => fieldIds.Contains("," + ((int)e).ToString() + ",")).Select(e => e.ToString().Replace("_", ""));
string select = "new ( TaskId, " + (taskColum.Count() > 0 ? string.Join(", ", taskColum) + ", " : "") + "Id )";
return db.Task.ToList().Select(t => new DynamicColumns() { Id = t.Id, TaskId = Project != null ? Project.Alias + "-" + t.Id : t.Id.ToString(), ActualTime = t.ActualTime, AssignedBy = t.AssignedBy.ToString(), AssignedDate = t.AssignedDate, AssignedTo = t.AssignedTo.ToString(), CreatedDate = t.CreatedDate, Details = t.Details, EstimatedTime = t.EstimatedTime, FileName = t.FileName, LogWork = t.LogWork, Module = t.Module != null ? t.Module.Name : "", Priority = t.Priority != null ? t.Priority.Name : "", Project = t.Project != null ? t.Project.Name : "", ResolveDate = t.ResolveDate, Status = t.Status != null ? t.Status.Name : "", Subject = t.Subject, TaskType = t.TaskType != null ? t.TaskType.Type : "", Version = t.Version != null ? t.Version.Name : "" }).ToList().AsQueryable().Select(select);
}
}
任何有这方面经验的人都可以权衡吗？ 我也检查了ScottGu的博客，但似乎缺少或不理解的东西。
编辑 ：
编辑为空间
编辑2 ：
使用从DynamicSelectionColumns()返回到我的变量selectStatement ，我有以下代码：
public IQueryable DynamicSelectionColumns(List<string> fieldsForExport)
{
using (var db = new InventoryTrackerContext())
{
string fieldIds = "," + "4,5,3,2,6,17,11,12" + ",";
var taskColum = Enum.GetValues(typeof(EnumTasks)).Cast<EnumTasks>().Where(e => fieldIds.Contains("," + ((int)e).ToString() + ",")).Select(e => e.ToString().Replace("_", ""));
//string select = "new ( TaskId, " + (taskColum.Count() > 0 ? string.Join(", ", taskColum) + ", " : "") + "Id )";
string select = "new ( " + string.Join(", ", fieldsForExport) + ")";
//return db.INV_Assets.ToList().Select(t => new DynamicColumns() { Id = t.Id, TaskId = Project != null ? Project.Alias + "-" + t.Id : t.Id.ToString(),
return db.INV_Assets.ToList().Select(t => new DynamicColumns() {
Id = t.Id,
Manufacturer = Convert.ToString(t.Manufacturer.manufacturer_description),
Type = t.Type.type_description,
Location = t.Location.location_room,
Vendor = t.Vendor.vendor_name,
Status = t.Status.status_description,
ip_address = t.ip_address,
mac_address = t.mac_address,
note = t.note,
owner = t.owner,
//Module = t.Module != null ? t.Module.Name : "",
cost = t.cost,
po_number = t.po_number,
description = t.description,
invoice_number = t.invoice_number,
serial_number = t.serial_number,
asset_tag_number = t.asset_tag_number,
acquired_date = t.acquired_date,
disposed_date = t.disposed_date,
verified_date = t.verified_date,
created_date = t.created_date,
created_by = t.created_by,
modified_date = t.modified_date,
modified_by = t.modified_by
}).ToList().AsQueryable().Select(select);
}
}
[HttpPost]
public ActionResult ExportUsingEPPlus(ExportAssetsViewModel model)
{
ExcelPackage package = new ExcelPackage();
var ws = package.Workbook.Worksheets.Add("TestExport");
var exportFields = new List<string>();
foreach(var selectedField in model.SelectedFields)
{
// Adds selected fields to [exportFields] List<string>
exportFields.Add(model.ListOfExportFields.First(s => s.Key == selectedField).Value);
}
var selectStatement = DynamicSelectionColumns(exportFields);
// Loops to insert column headings into Row 1 of Excel
for (int i = 0; i < exportFields.Count(); i++ )
{
ws.Cells[1, i + 1].Value = exportFields[i].ToString();
}
if (selectStatement.Count() > 0)
{
ws.Cells["A2"].LoadFromCollection(selectStatement.ToString());
}
var memoryStream = new MemoryStream();
package.SaveAs(memoryStream);
string fileName = "Exported-InventoryAssets-" + DateTime.Now + ".xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
memoryStream.Position = 0;
return File(memoryStream, contentType, fileName);
}
这会产生一个带有[ip_address]，[mac_address]，[note]，[owner]和[cost]（我选择的字段）列的Excel输出，但不包含数据。 而不是数据，我得到列A中的251行0和其他内容中没有。
我如何在我的Excel电子表格中实现动态选择查询结果？
编辑3 ：
试图ThulasiRam的建议（下面的ExportController）：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using InventoryTracker.DAL;
using OfficeOpenXml;
using InventoryTracker.Models;
using System.Linq.Dynamic;
namespace InventoryTracker.Controllers
{
public class ExportController : Controller
{
InventoryTrackerContext _db = new InventoryTrackerContext();
public static List<DynamicColumns> DynamicColumnsCollection = new List<DynamicColumns>();
[HttpPost]
public ActionResult ExportUsingEPPlus（ExportAssetsViewModel model）{// FileInfo newExcelFile = new FileInfo（output）; ExcelPackage包=新的ExcelPackage（）; var ws = package.Workbook.Worksheets.Add（“TestExport”）;
var exportFields = new List<string>();
foreach(var selectedField in model.SelectedFields)
{
// Adds selected fields to [exportFields] List<string>
exportFields.Add(model.ListOfExportFields.First(s => s.Key == selectedField).Value);
}
int cnt = 0;
foreach(var column in exportFields)
{
DynamicColumnsCollection.Add(new DynamicColumns()
{
Id = cnt,
ip_address = "ip_address" + cnt,
mac_address = "mac_address" + cnt,
note = "note" + cnt,
owner = "owner" + cnt,
cost = "cost" + cnt,
po_number = "po_number" + cnt,
description = "description" + cnt,
invoice_number = "invoice_number" + cnt,
serial_number = "serial_number" + cnt,
asset_tag_number = "asset_tag_number" + cnt,
acquired_date = "acquired_date" + cnt,
disposed_date = "disposed_date" + cnt,
verified_date = "verified_date" + cnt,
created_date = "created_date" + cnt,
created_by = "created_by" + cnt,
modified_date = "modified_date" + cnt,
modified_by = "modified_by" + cnt
});
}
//var selectStatement = DynamicSelectionColumns(exportFields);
IQueryable collection = DynamicSelectionColumns(new List<string>() {
"id",
"owner",
"note"
});
// Loops to insert column headings into Row 1 of Excel
for (int i = 0; i < exportFields.Count(); i++ )
{
ws.Cells[1, i + 1].Value = exportFields[i].ToString();
}
ws.Cells["A2"].LoadFromCollection(collection.ToString());
// ws.Cells["A2"].LoadFromCollection(selectStatement.ToString());
var memoryStream = new MemoryStream();
package.SaveAs(memoryStream);
string fileName = "Exported-InventoryAssets-" + DateTime.Now + ".xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
memoryStream.Position = 0;
return File(memoryStream, contentType, fileName);
}
public IQueryable DynamicSelectionColumns(List<string> fieldsForExport)
{
using (var db = new InventoryTrackerContext())
{
if (!fieldsForExport.Any())
{
return null;
}
string select = string.Format("new ( {0} )", string.Join(", ", fieldsForExport.ToArray()));
var collection = DynamicColumnsCollection.Select(t => new DynamicColumns()
{
Id = t.Id,
//Manufacturer = Convert.ToString(t.Manufacturer.manufacturer_description),
//Type = t.Type.type_description,
//Location = t.Location.location_room,
//Vendor = t.Vendor.vendor_name,
//Status = t.Status.status_description,
ip_address = t.ip_address,
mac_address = t.mac_address,
note = t.note,
owner = t.owner,
//Module = t.Module != null ? t.Module.Name : "",
cost = t.cost,
po_number = t.po_number,
description = t.description,
invoice_number = t.invoice_number,
serial_number = t.serial_number,
asset_tag_number = t.asset_tag_number,
acquired_date = t.acquired_date,
disposed_date = t.disposed_date,
verified_date = t.verified_date,
created_date = t.created_date,
created_by = t.created_by,
modified_date = t.modified_date,
modified_by = t.modified_by
}).ToList().AsQueryable().Select(select);
return collection;
}
}
public class DynamicColumns : INV_Assets
{
public string Model { get; set; }
public string Manufacturer { get; set; }
public string Type { get; set; }
public string Location { get; set; }
public string Vendor { get; set; }
public string Status { get; set; }
public string ip_address { get; set; }
public string mac_address { get; set; }
public string note { get; set; }
public string owner { get; set; }
public string cost { get; set; }
public string po_number { get; set; }
public string description { get; set; }
public string invoice_number { get; set; }
public string serial_number { get; set; }
public string asset_tag_number { get; set; }
public string acquired_date { get; set; }
public string disposed_date { get; set; }
public string verified_date { get; set; }
public string created_date { get; set; }
public string created_by { get; set; }
public string modified_date { get; set; }
public string modified_by { get; set; }
}
public enum EnumTasks
{
Model = 1,
Manufacturer = 2,
Type = 3,
Location = 4,
Vendor = 5,
Status = 6,
ip_address = 7,
mac_address = 8,
note = 9,
owner = 10,
cost = 11,
po_number = 12,
description = 13,
invoice_number = 14,
serial_number = 15,
asset_tag_number = 16,
acquired_date = 17,
disposed_date = 18,
verified_date = 19,
created_date = 20,
created_by = 21,
modified_date = 22,
modified_by = 23
}
我无法弄清楚的是在我的MVC应用程序中将这些相关的代码从他们的建议（或设置）中放到哪里：
static void Main(string[] args)
{
IQueryable collection = DynamicSelectionColumns(new List<string>() { "id", "name" });
Console.ReadLine();
}
有什么想法吗？ 我不知道如何构建用于我的MVC应用程序的示例中使用的static Program()或Main() 。 在我上面列出的代码中（我应该只选择note / owner字段），我会收到一个输出的Excel工作表，其中包含A1 "note" "owner" ， B1 "owner" ，然后是单元格A2:A180的数字0 A2:A180 ... ？
你得到的错误与linq或你导入到你项目中的其他库没有任何关系。
您在名称空间中声明了DynamicSelectionColumns函数，而不是在ExportController类中声明。
在您编辑之后：
如果您的exportFields已经是Task列的列表，那么您可以简单地将该列表传递给DynamicSelectionColumns然后在里面：
string select = "new ( " + string.Join(", ", exportFields) + ")";
Edit2之后：
更换
ws.Cells["A2"].LoadFromCollection(selectStatement.ToString());
同
ws.Cells["A2"].LoadFromCollection(selectStatement, false);
Edit3之后：
在这个试验和错误接近之后，我决定查找你提到的EPPlus库。 我发现你不需要这个DynamicQuery。 您可以在LoadFromCollection中指定要显示的字段。 我没有得到编译这个（因为它是你的代码），但它使用假数据在我的机器上工作。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using InventoryTracker.DAL;
using OfficeOpenXml;
using InventoryTracker.Models;
using System.Reflection;
using OfficeOpenXml.Table;
namespace InventoryTracker.Controllers
{
public class ExportController : Controller
{
private InventoryTrackerContext _db = new InventoryTrackerContext();
[HttpPost]
public ActionResult ExportUsingEPPlus(ExportAssetsViewModel model)
{
//FileInfo newExcelFile = new FileInfo(output);
ExcelPackage package = new ExcelPackage();
var ws = package.Workbook.Worksheets.Add("TestExport");
var exportFields = new List<string>();
foreach (var selectedField in model.SelectedFields)
{
// Adds selected fields to [exportFields] List<string>
exportFields.Add(model.ListOfExportFields.First(s => s.Key == selectedField).Value);
}
// Loops to insert column headings into Row 1 of Excel
for (int i = 0; i < exportFields.Count(); i++)
{
ws.Cells[1, i + 1].Value = exportFields[i].ToString();
}
var membersToShow = typeof(INV_Asset).GetMembers()
.Where(p => exportFields.Contains(p.Name))
.ToArray();
ws.Cells["A2"].LoadFromCollection(_db.INV_Assets.ToList(), false, TableStyles.None, BindingFlags.Default, membersToShow);
var memoryStream = new MemoryStream();
package.SaveAs(memoryStream);
string fileName = "Exported-InventoryAssets-" + DateTime.Now + ".xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
memoryStream.Position = 0;
return File(memoryStream, contentType, fileName);
}
}
}
1. TaskId property not exist in DynamicColumns class.
2. Remove .Replace("_", "") from
var taskColum = Enum.GetValues(typeof(EnumTasks)).Cast<EnumTasks>().Where(e => fieldIds.Contains("," + ((int)e).ToString() + ",")).Select(e => e.ToString().Replace("_", ""));
3.exportFields.Count should be > 0.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic;
namespace Dynamic
{
public class Program
{
public static List<DynamicColumns> DynamicColumnsCollection = new List<DynamicColumns>();
static Program()
{
for (int i = 0; i < 10; i++)
{
DynamicColumnsCollection.Add(new DynamicColumns() { Id = i, Name = "Name" + i, ip_address = "ip_" + i });
}
}
static void Main(string[] args)
{
IQueryable collection = DynamicSelectionColumns(new List<string>() { "id", "name" });
Console.ReadLine();
}
public class DynamicColumns
{
public int Id { get; set; }
public string Name { get; set; }
public string ip_address { get; set; }
}
public static IQueryable DynamicSelectionColumns(List<string> fieldsForExport)
{
if (!fieldsForExport.Any())
return null;
string select = string.Format("new ( {0} )", string.Join(", ", fieldsForExport.ToArray()));
var collection = DynamicColumnsCollection.Select(t => new DynamicColumns()
{
Id = t.Id,
Name = t.Name,
ip_address = t.ip_address,
}).ToList().AsQueryable().Select(select);
return collection;
}
}
}
还有任何问题让我知道。
问候，
Ram.S
链接地址: http://www.djcxy.com/p/72027.html上一篇: Implementing Dynamic LINQ querying in MVC5/EF Application?