As a review, I am trying to add Export() functionality to my application - allowing the user to specify certain model fields and export values only in these fields, querying LINQ and using EPPlus for export. I am trying to implement Dynamic LINQ functionality in my MVC5 / EF Code-First application based on THIS , but there seems to be something missing to make it work or not to understand something.
First I added a new class file to my main project folder called DynamicLibrary.cs . When I download the .zip HERE , I believe that the code I wanted was the code of the Dynamic.cs file that I copied into DynamicLibrary.cs in my project. This allowed me to specify using System.Linq.Dynamic in my project.
Now I'm stuck trying to figure out how to set up the rest for Dynamic LINQ.
In my ExportController in namespace InventoryTracker.Controllers {} , but outside the public class ExportController : Controller { } I added example code based on the fields in my INV_Assets model, which I am trying to export:
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); } } }
I am not 100% sure that this is set up in the right place. The last method below has 5 errors:
IQueryable - Expected class, delegate, enum, interface, or struct.InventoryTrackerContext - Expected class, delegate, enum, interface, or struct.DynamicColumns() - Expected class, delegate, enum, interface, or struct.- Closing
} for public IQueryable DynamicSelectionColumns() - Type or namespace definition, or end-of-file expected. Closing } for 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); } }
Can anyone with more experience with such a weighting? I also checked the ScottGu Blog , but it seems to be missing or something I didn’t understand.
EDIT
DISABLED FOR SPACE
EDIT2
Using the return from DynamicSelectionColumns() to my selectStatement variable, I have the following code:
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); }
This gives Excel output with the columns [ip_address], [mac_address], [note], [owner] and [cost] (the fields I selected), but without data. Instead of data, I get 251 row 0 in column A and nothing else.
How to implement dynamic query selection results in my Excel spreadsheet?
EDIT3
Try ThulasiRam suggestion (ExportController below):
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 (model ExportAssetsViewModel) {// 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); } 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 }
What I cannot understand is where to put this important piece of code from my proposal (or configure it) in my MVC application:
static void Main(string[] args) { IQueryable collection = DynamicSelectionColumns(new List<string>() { "id", "name" }); Console.ReadLine(); }
Any thoughts? I am not sure how to structure the static Program() or Main() used in the example for my MVC application. In my code above (I should only select the note / owner field), I get an Excel output sheet with "note" in A1 , "owner" in B1 , and then just the number 0 in cells A2:A180 ...?