ASP.NET MVC & Entity - Loading Navigation Properiies/Objects When Using Raw SQL

Question

I am working on an application that generates a "report" view based upon options the user selects in a form. As I am "dynamically' generating the necessary database query based upon user input, I have opted for using raw SQL as opposed to a LINQ query.

  [HttpPost]
   public  ActionResult GeneratedReport(String StartDate, String EndDate, String EquipmentID, String LocationID, String UserID )
    {
        String sqlQuery = String.Format("SELECT * from Records WHERE RecordDate BETWEEN '{0}' and '{1}'", StartDate, EndDate);
        GeneratedReportViewModel vm = new GeneratedReportViewModel();
        List<Record> records;

        bool userFilter = (UserID == "") ? false : true;
        bool locationFilter = (LocationID == "") ? false : true;
        bool equipmentFilter = (EquipmentID == "") ? false : true;

        if(userFilter) {
            sqlQuery += " AND UserID = " + UserID;
        } if(locationFilter) {
            sqlQuery += " AND LocationID = " + LocationID;
        } if(equipmentFilter) {
            sqlQuery += " AND EquipmentID = " + EquipmentID;
        }

        records = dbContext.Database.SqlQuery<Record>(sqlQuery).ToList();
       vm.FilteredRecords = records;

       return View("GeneratedReport", vm);

    }
}

The definition for the Entity generated Record class:

{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.Spatial;

    public partial class Record
    {
        public int RecordID { get; set; }

        public DateTime RecordDate { get; set; }

        public int EquipmentID { get; set; }

        public int ActionTypeID { get; set; }

        public int UserID { get; set; }

        [StringLength(200)]
        public string Notes { get; set; }

        public int LocationID { get; set; }

        public virtual Action Action { get; set; }

        public virtual Equipment Equipment { get; set; }

        public virtual Location Location { get; set; }

        public virtual User User { get; set; }
    }
}

However, whenever the server attempts to load the view associated with GeneratedReport (code follows), all the navigation properties (User, Location, Equipment, Action) of the Record objects are null.

@model IT_Equipment_Log.ViewModels.GeneratedReportViewModel

@{
    ViewBag.Title = "GeneratedReport";
}

<table>
    <tr><th>Equipment</th><th>User</th><th>Date</th><th>Location</th><th>Action</th><th>Notes</th></tr>

    @foreach (var record in @Model.FilteredRecords)
    {
        <tr><td>@record.Equipment.SerialNumber</td><td>@record.User.FullName</td><td>@record.RecordDate</td><td>@record.Location.DisplayName</td><td>@record.Action.Description</td><td>@record.Notes</td></tr>
    }

</table>

I cannot figure out how to make Include() work in this situation, nor an easy way to convert this SQL statement (since it will vary based upon user input) into a LINQ query. Are there any other options available, or how might I resolve this issue?

Thanks much.

http://stackoverflow.com/questions/32054887/loading-navigation-properties-with-raw-sql-query — flofreelance, Dec 30 '16 at 15:31
I did not think of that federico scamuzzi. I might have to look into that sometime; it seems like it would be efficient way to accomplish the task if it works. Thanks. — KellyM, Dec 30 '16 at 16:09

score 2 · Accepted Answer · answered Dec 30 '16 at 15:54

2

I try to rewrite your code by using LINQ. guess it will get the navigation properties that you want.

var records = dbContext.Record.Where(p => p.RecordDate >= StartDate && p.RecordDate <= EndDate);
if(!string.IsNullOrEmpty(UserID)){
    records.Where(p => p.UserID = UserID);
}

if(!string.IsNullOrEmpty(LocationID)){
    records.Where(p => p.LocationID = LocationID);
}

if(!string.IsNullOrEmpty(EquipmentID)){
    records.Where(p => p.EquipmentID = EquipmentID);
}

var vm = vm = new GeneratedReportViewModel{
    FilteredRecords = records
};

return View("GeneratedReport", vm);

answered Dec 30 '16 at 15:54

kcwu

219
2
6

Sincerely, thank you so much. :) It may require a few modifications for my exact needs, but it certainly got me on the correct path. Thanks again. – KellyM Dec 30 '16 at 16:07
Thank you, it's my pleasure. – kcwu Dec 31 '16 at 11:41

score 1 · Answer 2 · edited May 23 '17 at 12:24

As also indicated in this post: https://stackoverflow.com/a/7582596/3762855 seems that it is not possible to "include" entity related collections, through sql directely injected by code, unless you integrate your query with some "join" statements as Federico Scamuzzi commented...

But you may try to do your work with Linq so you can include your related collections:

var qryRecords = from rec in dbContext.Records
           .Include(rec => c.Action)
           .Include(rec => c.Equipment)
           .Include(rec => c.Location)
           .Include(rec => c.User)
           .Where(rec => rec.RecordDate >= StartDate && rec.RecordDate <= EndDate) //it needs manages string date comparing with close attention, better it would be if dates were DateTime...

bool userFilter = (UserID == "") ? false : true;
bool locationFilter = (LocationID == "") ? false : true;
bool equipmentFilter = (EquipmentID == "") ? false : true;

var filterdQuery = from rec in qryRecords
                   where (userFilter ? rec.UserID == UserID : 1 == 1)
                      && (locationFilter ? rec.LocationID == LocationID : 1 == 1)
                      && (equipmentFilter ? rec.EquipmentID == EquipmentID : 1 == 1);

vm.FilteredRecords = records.ToList();

Thanks! Looks very clean and elegant. I actually already incorporated kcwu answers, but I am quite confident this would work as well. I suppose LINQ is a bit more flexible than I realized. — KellyM, Dec 30 '16 at 16:13

ASP.NET MVC & Entity - Loading Navigation Properiies/Objects When Using Raw SQL

2 Answers2