Implementing Dynamic LINQ querying in MVC5/EF Application?

As an overview I am attempting to add Export() functionality to my application -- allowing the user to specify certain model fields and only export the values in those fields by querying with LINQ and using the EPPlus library to Export. I am attempting to implement Dynamic LINQ functionality in my MVC5/EF Code-First application based on THIS example, but seem to be missing some things to get it working or not understanding something.

First I added a new class file to my main project folder called DynamicLibrary.cs . When I download the .zip HERE, I "believe" the code I wanted was the Dynamic.cs file code which I copied into DynamicLibrary.cs in my project. Doing this allowed me to reference using System.Linq.Dynamic in my project.

Now I'm stuck trying to figure out how to setup the rest for Dynamic LINQ.

In my ExportController within the namespace InventoryTracker.Controllers {} but outside the public class ExportController : Controller { } I added the example code based upon the fields in my INV_Assets model I am attempting 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 this is set up in the right location. The last method below has 5 errors:

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 in this kind of thing weigh-in? I also checked out ScottGu's Blog, but seem to be missing or not understanding something.

EDIT :

REDACTED FOR SPACE

EDIT2 :

Using the return from DynamicSelectionColumns() into my variable selectStatement , I have the following coded:

    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 yields an Excel output with columns [ip_address], [mac_address], [note], [owner], and [cost] (the fields I selected), but no data. Instead of data, I get 251 rows of 0 in column A and nothing in the others.

How do I implement the dynamic select query results into my Excel spreadsheet?

EDIT3 :

Attempting ThulasiRam's 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(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);
    }

    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 can't figure out is where to put this relevant piece of code from their suggestion (or set it up) within my MVC application:

    static void Main(string[] args)
    {
        IQueryable collection = DynamicSelectionColumns(new List<string>() { "id", "name" });

        Console.ReadLine();
    }

Any thoughts? I'm not sure how to structure the static Program() or Main() used in the example for my MVC app. In my code listed above (should I select just the note / owner field), I receive an output Excel sheet with "note" in A1 , "owner" in B1 , and then just the number 0 in cells A2:A180 ...?

The errors you're getting has nothing to do with linq or the other libraries you imported into your project.

You're declaring the function DynamicSelectionColumns in the namespace, not in the ExportController class.

After your Edit:

if your exportFields is already a list of Task columns, you can simply pass that list to DynamicSelectionColumns and have this inside:

string select = "new (  " + string.Join(", ", exportFields) + ")";

After Edit2:

Replace

ws.Cells["A2"].LoadFromCollection(selectStatement.ToString());

with

ws.Cells["A2"].LoadFromCollection(selectStatement, false);

After Edit3:

After this trial and error approaches, I decided to indeed lookup the EPPlus library you mentioned. I found out that you don't need any of this DynamicQuery. You can specify into LoadFromCollection the fields you want to show. I didn't get to compile this (because it's your code) but It worked on my machine with fake data.

    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;
        }
    }
}

Further any problem let me know.

Regards,

Ram.S

上一篇: 在EPPlus中自动调整行

下一篇: 在MVC5 / EF应用程序中实现动态LINQ查询？