DbContext doesn't give any data

Question

I am using EntityFramework in my ASP .Net MVC project. The problem is I am not getting any data from the Database at all. My Database is a localDB and following is the connection string I am using -

<connectionStrings>
<add name="PortfolioDBContext" connectionString="Data Source=(localdb)\v11.0;Initial Catalog=NoobMVC;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

Following is my Db Context implementation -

public class PortfolioDBContext : DbContext
    {
        public PortfolioDBContext()
        {
            Debug.Write("Noob CONNNNN  "+Database.Connection.ConnectionString);
        }
        public DbSet<Product> Portfolio { get; set; }
    }

And this is what my controller looks like -

public class HomeController : Controller
    {
        // GET: Home
        public ActionResult Index()
        {
            PortfolioDBContext data = new PortfolioDBContext();
            Debug.Write("Data size  " + data.Portfolio.Count()); //This prints 0
            return View(data);
        }
    }

I am not sure what else I need to do in order to get data in the DbSet. Am I missing any step here or is there any way to debug the exact issue? I've already searched on SO and looks like I am the only one who's stuck here.

Update:
I've already tried sending various way to send data to the view. The main problem lies with the context, not with the view. I am not getting any data in the context, thus how I send this data to view doesn't matter.

Update 2:- When tried to log the queries using ChrFin's method, I got the following logs -

Opened connection at 16-10-2014 06:32:07 PM +05:30


SELECT Count(*)
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_SCHEMA + '.' + t.TABLE_NAME IN ('dbo.Products')
    OR t.TABLE_NAME = 'EdmMetadata'


-- Executing at 16-10-2014 06:32:08 PM +05:30

-- Completed in 21 ms with result: 1



Closed connection at 16-10-2014 06:32:08 PM +05:30

Opened connection at 16-10-2014 06:32:08 PM +05:30

SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[__MigrationHistory] AS [Extent1]
        WHERE [Extent1].[ContextKey] = @p__linq__0
    )  AS [GroupBy1]


-- p__linq__0: 'Noob_MVC.Models.PortfolioDBContext' (Type = String, Size = 4000)

-- Executing at 16-10-2014 06:32:08 PM +05:30

-- Completed in 21 ms with result: SqlDataReader



Closed connection at 16-10-2014 06:32:08 PM +05:30

Opened connection at 16-10-2014 06:32:08 PM +05:30

SELECT TOP (1) 
    [Project1].[C1] AS [C1], 
    [Project1].[MigrationId] AS [MigrationId], 
    [Project1].[Model] AS [Model], 
    [Project1].[ProductVersion] AS [ProductVersion]
    FROM ( SELECT 
        [Extent1].[MigrationId] AS [MigrationId], 
        [Extent1].[Model] AS [Model], 
        [Extent1].[ProductVersion] AS [ProductVersion], 
        1 AS [C1]
        FROM [dbo].[__MigrationHistory] AS [Extent1]
        WHERE [Extent1].[ContextKey] = @p__linq__0
    )  AS [Project1]
    ORDER BY [Project1].[MigrationId] DESC


-- p__linq__0: 'Noob_MVC.Models.PortfolioDBContext' (Type = String, Size = 4000)

-- Executing at 16-10-2014 06:32:08 PM +05:30

-- Completed in 17 ms with result: SqlDataReader



Closed connection at 16-10-2014 06:32:08 PM +05:30

Opened connection at 16-10-2014 06:32:08 PM +05:30

SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[Products] AS [Extent1]
    )  AS [GroupBy1]


-- Executing at 16-10-2014 06:32:08 PM +05:30

-- Completed in 10 ms with result: SqlDataReader



Closed connection at 16-10-2014 06:32:08 PM +05:30

Noob context size  0Opened connection at 16-10-2014 06:32:08 PM +05:30

'iisexpress.exe' (CLR v4.0.30319: /LM/W3SVC/21/ROOT-1-130579381242638924): Loaded 'EntityFrameworkDynamicProxies-Noob MVC'. 
SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Description] AS [Description], 
    [Extent1].[Link] AS [Link], 
    [Extent1].[SmallImageLink] AS [SmallImageLink], 
    [Extent1].[LargeImageLink] AS [LargeImageLink]
    FROM [dbo].[Products] AS [Extent1]


-- Executing at 16-10-2014 06:32:08 PM +05:30

-- Completed in 11 ms with result: SqlDataReader



Closed connection at 16-10-2014 06:32:08 PM +05:30

I am sure something is fishy here. The name of the table is Portfolio, not Products.

Update 3:-
The issue got solved finally. Please check my answer below for details.

Is your connectionstring matching the one in your web.config on debug output? — Stan, Oct 16 '14 at 12:53
Yes, it matches exactly. Here's the log I am getting - Noob CONNNNN Data Source=(localdb)\v11.0;Initial Catalog=NoobMVC;Integrated Security=True' — 0xC0DED00D, Oct 16 '14 at 12:55

Christoph Fink · Answer 1 · 2014-10-16T13:32:24.457

1

I think what you want is something like:

public ActionResult Index()
{
    using (var data = new PortfolioDBContext())
    {
        var model = data.Portfolio.ToList(); // ToList so the controller queries the DB
        // does "model" have anything in it here?
        return View(model);
    }
}

UPDATE.
Try to set the following:

public class PortfolioDBContext : DbContext
{
    public PortfolioDBContext()
    {
        Database.Log = s => Debug.WriteLine(s);
    }
    public DbSet<Product> Portfolio { get; set; }
}

and check what queries are sent by EF. Are they correct?

UPDATE 2:
You can also do the following if you don't like the table name created via convention:

[Table("Portfolio")]
public class Product { /* ... */ }

FYI: By convention the table is named after the plural of the entities name.

edited Oct 16 '14 at 13:32

answered Oct 16 '14 at 12:47

Christoph Fink

22,727
9
68
113

The problem is not with how I pass the data to the view. I am not getting data in the context itself. The size of DbSet is 0; – 0xC0DED00D Oct 16 '14 at 12:49
You don't need to do `ToList();` when passing to view, it's actually ver bad because it slows down process a lot. – Stan Oct 16 '14 at 12:49
@Steve can you provide documentation on that? I've never heard that before. – Jonesopolis Oct 16 '14 at 12:50
`You don't need to do ToList(); when passing to view,` - yes you do, when you use the `using` as the context will dispose. `it's actually ver bad because it slows down process a lot.` - why whould it slow down? It's actually bad-practice to NOT do so. The view should only display and no "get data"... – Christoph Fink Oct 16 '14 at 12:52
@Jonesy TL;DR is that `ToList();` will query the whole set, including all sub-parts. When you just pass it as is it won't query anything and will take advantage of Lazy Loading e.g. querying only what needs to be queried. – Stan Oct 16 '14 at 12:52
@Steve: ...and thats only true, when you don't need the whole list, which was never said by the OP. And even if you don't need the whole data-set you should filter it in the controller (or a helper) and not in the view. Also "sub-parts" are not loaded if you don't define any eager-loading. And last but not least, what you mean is "deffered execution" and not "lazy loading"... – Christoph Fink Oct 16 '14 at 12:56
@ChrFin Thanks, I've posted the log in the Question. I suppose it's taking the table name wrong. – 0xC0DED00D Oct 16 '14 at 13:06
Thanks, the problem is solved now. I've posted an answer mentioning the exact issue. – 0xC0DED00D Oct 16 '14 at 13:19
@noob: See my last update for an alternative solution. – Christoph Fink Oct 16 '14 at 13:26
@ChrFin Oh! I actually posted another question on how to use multiple tables with same dbcontext - http://stackoverflow.com/questions/26405685/how-to-specify-the-name-of-the-table-to-be-used-by-dbcontext – 0xC0DED00D Oct 16 '14 at 13:28

samy · Answer 2 · 2014-10-16T12:56:36.990

0

You are passing the whole context to the View. Unless you are referencing the Portfolio property inside the view I think you need to pass the portfolio directly:

return View(data.Portfolio);

From the updated code, I see you are never passing the connection string to the DbContext. Use one of the constructors that uses the connection string (the simplest being the one taking a string, with the string as the connection string)

edited Oct 16 '14 at 12:56

answered Oct 16 '14 at 12:45

samy

14,832
2
54
82

That was just to simplify the situation. I am actually using another class which queries the context data. The problem is the context itself doesn't get any data at all. So even if I pass the collection or the whole context doesn't matter. – 0xC0DED00D Oct 16 '14 at 12:46
4

that's a separate problem, not a simplification. You should post your actual code. – Jonesopolis Oct 16 '14 at 12:48
Please check the updated code then. I am using the actual code posted in the question which doesn't work. The size of context is zero. The data is definitely in the table. – 0xC0DED00D Oct 16 '14 at 12:54

score 0 · Answer 3 · answered Oct 16 '14 at 12:55

0

Try specifying name of the connectionstring directly like so.

public class PortfolioDBContext : DbContext
{
    public PortfolioDBContext() : base("PortfolioDBContext") { }

    public DbSet<Product> Portfolio { get; set; }
}

answered Oct 16 '14 at 12:55

Stan

25,744
53
164
242

Then I'd have to guess your database is just empty. Are you 100% sure you have data in it? – Stan Oct 16 '14 at 13:05
Yes there's data in their. I have just tried adding more sample rows in it and it's still showing 0 data – 0xC0DED00D Oct 16 '14 at 13:07

score 0 · Accepted Answer · answered Oct 16 '14 at 13:18

Thanks to all of the answer, I got the issue solved. The database was trying to use an old deleted table named 'Products' instead of 'Portfolio'. So I deleted the table in the SQL Server Object Explorer and renamed the 'Portfolio' table to 'Products' and the DbContext started showing the result.

I thought the name of the DbSet variable decides the actual table name to be used. But I suppose it doesn't work that way.

PS: Thanks to ChrFin whose log code lead to solving the issue.

DbContext doesn't give any data

4 Answers4

Linked