Retrieve data from SQL view inside ASP.NET MVC application

Question

I've read all questions related to this question, all videos I could find, and I couldn't find a solution to my problem in any of them.

I've been trying to display data from a SQL view in a .cshtml page using the ASP.NET MVC framework.

This is the code in my controller:

public ActionResult childinfo()
{
       var myQuery1 = db.WebChildCaretakerInfo.Where(s => s.CustSSN == Custssn).Select(s => new { FirstName = s.FirstName, age = s.age, program_name = s.program_name });

        return View(myQuery1);
}

where WebChildCaretakerInfo is the view I want to get data from.

This is the code inside my cshtml page:

@model IEnumerable<WebProgProject.Models.WebChildCaretakerInfo>

@{Layout = null;}

@{ 
var childName = "";
var age = 0;
var caretakerName = "";
var caretakerEmail = "";
var caretakerPhone = "";
foreach (var item in Model)
{
    childName = @item.FirstName;
    age = (int)@item.age;
    caretakerName = @item.CaretakerName;
    caretakerEmail = @item.email;
    caretakerPhone = @item.phone_number;}
}

Note: I'm using ChildName and the other variables in read-only textboxes later in the code.

When I try to execute this page, I get this error in the browser:

The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery1[<>f__AnonymousType33[System.String,System.Nullable1[System.Int32],System.String]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable1[WebProgProject.Models.WebChildCaretakerInfo]'.

Exception Details: System.InvalidOperationException: The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery1[<>f__AnonymousType33[System.String,System.Nullable1[System.Int32],System.String]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable1[WebProgProject.Models.WebChildCaretakerInfo]'.

I tried removing the header

@model IEnumerable<WebProgProject.Models.WebChildCaretakerInfo>

and executing, I get a different error:

Object does not contain a definition for FirstName in childName = @item.FirstName

which of course is nonsense because it's right here in my WebChildCaretakerInfo.cs file:

namespace WebProgProject.Models
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;

    public partial class WebChildCaretakerInfo
    {
        public string FirstName { get; set; }
        public Nullable<int> age { get; set; }
        public string programCode { get; set; }
        public string CaretakerName { get; set; }
        public string email { get; set; }
        public string phone_number { get; set; }
        public string program_name { get; set; }
        public string SSN { get; set; }
        public string CustSSN { get; set; }
    }
}

Is there a different way of retrieving data from a SQL view into an ASP.NET MVC application that is different to retrieving data from a SQL table? Because in the same application, I'm retrieving data from tables and it's no problem.

David · Accepted Answer · 2019-12-22 18:20:59Z

1

You're expecting a specific type:

@model IEnumerable<WebProgProject.Models.WebChildCaretakerInfo>

But selecting an anonymous type:

.Select(s => new { FirstName = s.FirstName, age = s.age, program_name = s.program_name })

Basically, an anonymous type isn't going to work here. Because the view needs to know what type to expect. If you need a custom type then you can create a view model, but based on the names it sounds like the type you want is exactly what's being queried from your database. So just remove the .Select() clause:

var myQuery1 = db.WebChildCaretakerInfo.Where(s => s.CustSSN == Custssn);
return View(myQuery1);

This will return an enumeration of WebChildCaretakerInfo, which is exactly what the view expects.

(Note that if you do create a view model then you may not be able to select it directly from your database, as the creation of the view model might not be something LINQ to Entities can translate to the underlying data store. In that case you would need to introduce an intermediary step, which may just take the form of a .ToList(), which materializes the results into memory from which you'd then convert to your view model type.)

answered Dec 22, 2019 at 18:20

David

221k42 gold badges245 silver badges337 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SAM Over a year ago

So this works as long as I don't include the @model header in my view. When I add it, it gives me this exception: The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery`1[WebProgProject.Models.WebChildCaretakerInfo]', but this dictionary requires a model item of type 'WebProgProject.Models.WebChildCaretakerInfo'.

David Over a year ago

@Sam: It sounds like your @model directive may be confusing the difference between an instance of WebChildCaretakerInfo and a collection of WebChildCaretakerInfo. The error suggests that you're telling the view to expect just a single instance.

SAM Over a year ago

Aha! I would never have understood that from the exception string itself. I got it working now by saying @model IEnumerable<WebProgProject.Models.WebChildCaretakerInfo>, emphasising that it is a list not a single value. Thanks!

Collectives™ on Stack Overflow

Retrieve data from SQL view inside ASP.NET MVC application

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related