0

I am creating a simple website in DotNET Core, mainly to learn as I want to move away from PHP.

I wanted to run simple raw query to fill one table with some data. I also have few stored procedures that I would like to occasionally run.

I have found this learning material and tried to follow it (Updated in October this year): https://msdn.microsoft.com/en-us/library/jj592907(v=vs.113).aspx

After getting errors I realised that latest version of EF does not contain definition for SqlQuery, and the closest you get is FromSQL.

I've found this issue discussion on github: https://github.com/aspnet/EntityFramework/issues/1862 And if you follow the conversation there you will see that support for raw SQL queries will be added in the next version coming out in Q4 / 2016 - Q1 / 2017

Question:

I seem to be getting a lot of partial information on how to work around this problem and I am getting some sort of semblance of what needs to be done. Still I cant get my head around the whole concept.

I have this simple DB:

AutoId  Numbers
1       1
2       2
3       3
4       4
5       5
6       6
7       5
8       4
9       3
10      2
11      1

I've tried to access this data by writing this in my controller: 

var graphs = _context.Graph.FromSql("SELECT * FROM dbo.Graph").ToArray();

Then I passed it in ViewBag:

ViewBag.GraphData = graphs;

But when I try to emit(is that the right word here?) the data to the view, I see this (I put it there in the view just to see what will happen really):

System.Collections.Generic.List`1[ASPNET_Core_1_0.Models.Graph]


ASPNET_Core_1_0.Models.Graph 
ASPNET_Core_1_0.Models.Graph 
ASPNET_Core_1_0.Models.Graph 
ASPNET_Core_1_0.Models.Graph 
ASPNET_Core_1_0.Models.Graph 
ASPNET_Core_1_0.Models.Graph 
ASPNET_Core_1_0.Models.Graph 
ASPNET_Core_1_0.Models.Graph 
ASPNET_Core_1_0.Models.Graph 
ASPNET_Core_1_0.Models.Graph 
ASPNET_Core_1_0.Models.Graph

My View file looks like this:

@model IEnumerable<ASPNET_Core_1_0.Models.Graph>

@{
    ViewBag.Title = "Graphs";
}


@{ 
    var data = ViewBag.GraphData;
}
<h2>Graphs</h2>

<div> @ViewBag.GraphData </div>
<br />
<div>
    @foreach (var item in data) { 
        <br />
        @item
    }
</div>

====

Here is example from GIThub issue page:

E.g. assuming Product is an entity type and ProductListEntry is just an arbitrary CLR type that is not mapped in the mode, this works:

var data = db.Set<Product>()
.FromSql("SELECT * FROM Product WHERE 1=1")
.Select(t => new ProductListEntry{Id = t.Id, Name = t.Name})
.ToList();

But this doesn't:

var data = db.Set<ProductListEntry>()
.FromSql("SELECT Id, Name FROM Product WHERE 1=1")
.ToList();

Would anybody be kind enough to show me how to run this query and get actual results into my view instead of the object name? I understand that this might be dead easy question for some - for some reason I really struggle with understanding some of the basic concepts of DotNET Core and strongly typed language like C# hence questions like this.

Research:

Custom Data Access in EF for POCO

Raw SQL Query without DbSet - Entity Framework Core

What is a CLR class?

Context does not contain a definition for ExecuteStoreCommand

Entity Framework 7 FromSql stored procedure return value

Entity Framework Core 1.1 Plans:

https://blogs.msdn.microsoft.com/dotnet/2016/07/29/entity-framework-core-1-1-plans/

Community
  • 1
  • 1
PawelKosi
  • 151
  • 1
  • 3
  • 13
  • You can't, not implemented currently, but on roadmap. At least not in the latest stable version, which is EntityFrameworkCore 1.0. There is a 1.1-preview1 but its not finished yet, so there is no guarantee it work in there yet. In 1.0 only raw queries on mapped entities work – Tseng Nov 11 '16 at 17:07
  • So there is completely no way of say running a stored procedure in EF 7 at the moment? – PawelKosi Nov 11 '16 at 17:10
  • @SPs are on 1.1 roadmap too. You can try getting the 1.1-preview and see how well it works, but depending on your database provider there may not be one yet which supports 1.1 – Tseng Nov 11 '16 at 17:12
  • As a side note in your example above you are trying to return the models in the view `@item`, but unless you override `ToString()` method, it will only return the namespace+name. You should try to read the individual properties of the `Graph` class – Tseng Nov 11 '16 at 17:41
  • Any particular reason you want to use SQL and not just use EF as it's intended (an ORM)? so instead of `var graphs = _context.Graph.FromSql("SELECT * FROM dbo.Graph").ToArray();` just use `var graphs = context.Graph.ToArray()` I might have missed something in your objectives, but if you know what the schema looks like and have POCO classes defined, you shouldn't need to use SQL anywhere. `var singleRow = context.Graph.Single(x => x.Id == 4);` for example – Russell Young Nov 12 '16 at 07:42
  • Hey @RussellYoung - I need to access stored procedures to recalculate certain tables. Idea is to use this in accounting software in the future. I can do all sorts of magic in SQL server that I do not need to worry about in the app itself, and instead I can just focus on displaying data. Thank you for your reply though. – PawelKosi Nov 12 '16 at 11:21
  • @Tseng you mentioned _...only raw queries on mapped entities work_ would you be ok showing me example of that? Wouldn't that be a kind of workaround. Sorry I didn't focused on that when replying to you yesterday. – PawelKosi Nov 12 '16 at 12:16
  • @PawelKosi: You already quoted it in your question. `db.Set().FromSql("...")` where product is a `DbSet` on your DbContext or configured in the `OnModelCreating` method. If its not configured there it can't be used right now in EFCore 1.0. EFCore 1.1 is planed to make the later one work – Tseng Nov 12 '16 at 12:21
  • @Tseng Thanks, you gave me enough to go and explore other possibilities there. – PawelKosi Nov 12 '16 at 12:37
  • For the record, instead of using `@item` you should use something like: `@item.AutiId, @item.Numbers` to render the view details. – user2173353 Mar 28 '19 at 10:34

0 Answers0