Entity Framework bind raw SQL query to model class

Question

I got assignment to develop football table management system. I decided to do it using asp.net mvc. Only requirement is to use raw SQL queries. So that means I can't use linq or lambda. I would like to do something like this:

using (var context = new FootballTableContext())
{
     var players = context.Database.SqlQuery<PlayerViewModel>("SELECT Vardas, Pavarde FROM ZAIDEJAS").ToList();
}

but after executing this code, I Get a list of PlayerViewModel with null values.

ViewModel class:

public class PlayerViewModel
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Context class:

public class FootballTableContext : DbContext
{
    public FootballTableContext() : base("DefaultConnection") { }
}

So my question is how to bind that query to my ViewModel?

CodingYoshi · Accepted Answer · 2017-04-15 19:15:16Z

8

Do it like this:

var players = dbContext.Database
    .SqlQuery<PlayerViewModel>("SELECT Vardas as FirstName, Pavarde as LastName FROM ZAIDEJAS")
    .ToList<PlayerViewModel>();

It is known as SQL queries for non-entity types.

edited Apr 15, 2017 at 19:15

answered Apr 15, 2017 at 19:04

CodingYoshi

27.3k7 gold badges68 silver badges75 bronze badges

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

3 Comments

Mazas Over a year ago

But now it won't compile:

The type arguments for method 'Database.SqlQuery<TElement>(string, params object[])' cannot be inferred from the usage. Try specifying the type arguments explicitly.

CodingYoshi Over a year ago

Please see my edit. I am pretty sure it will work with just ToList() as long as the column names have been specified. I guess you can test both ways and let me know.

Mazas Over a year ago

Yes it works now. But maybe it is possible to add some attribute to viewmodel instead of using aliases?

Collectives™ on Stack Overflow

Entity Framework bind raw SQL query to model class

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related