Using IQueryable instead DbSet problem

Question

i stumbled to the next problem... I have database context:

// For support unit testing... 
public interface IDbContext : IDisposable
{
   IQueryable<Hardware> Hardwares { get; }
   IQueryable<ProviderHardware> ProviderHardwares { get; }
}

// Real DbContext (EF 4.0, Code First)
public class PrimaryDbContext : DbContext, IDbContext
{
   public DbSet<Hardware> Hardwares { get; set; }
   public DbSet<ProviderHardware> ProviderHardwares { get; set; }

   IQueryable<Hardware> IDbContext.Hardwares
     { get { return Hardwares; } }
   IQueryable<ProviderHardware> IDbContext.ProviderHardwares
     { get { return ProviderHardwares; } } 
   ...
}

And i try get all hardwares, which doesnt exists in ProviderHardwares table:

var hardwaresRemoved = db.Hardwares.Where(i => (i.IsAvailable == true) &&
   (db.ProviderHardwares.Count(j => j.Article == i.Article) == 0)).ToList();

If i use PrimaryDbContext strictly such as "PrimaryDbContext db = new PrimaryDbContext();" all work fine. But if i use it implicitly "IDbContext db = new PrimaryDbContext();" that i get an exception:

Unable to create a constant value of type 'ConfiguratorMvcApplication.DomainModels.ProviderHardware'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

Summarize, i can't replace a DbSet on an IQueryable. And how i can use unit testing in this case? I hope someone have resolved this problem yet... Thank in advance very much!

Are you declaring a constant anywhere in your `PrimaryDbContext`? — Joel C, Aug 21 '11 at 16:18
No, only that i demonstrate before... And override OnModelCreating method more... — xtmq, Aug 21 '11 at 16:46
I can reproduce this. I also saw (with SQL Profiler) that in the `IDbContext` case `db.ProviderHardwares` inside of the expression gets executed as a query which loads *all* `ProviderHardware` rows from the database (*as if* `IDbContext.ProviderHardwares` were an `IEnumerable` and not `IQueryable`). Then the `Count` and the outer query cannot be executed because the in memory list of objects (list of "constant" objects) cannot be used in SQL. This causes the exception. Why EF treats `IDbContext.ProviderHardwares` like an `IEnumerable` but not `PrimaryDbContext.ProviderHardwares`? No clue... — Slauma, Aug 21 '11 at 17:00
You are completely right, but... but why if i change property type, framework's behavour changes too so strange... And how others uses DbContext in unit tests?.. — xtmq, Aug 21 '11 at 19:47
I haven't used `DbContext`/`DbSet` from EF 4.1, I've only done this in EF 4.0 with `ObjectContext`/`ObjectSet`. I use `IObjectSet` in my interface, not `IQueryable`, and I don't explicitly map `IMyContext.EntitySet` to `MyContext.EntitySet`. — Joel C, Aug 21 '11 at 20:24
Hmm it may be possible solution, but i can't lead IObjectSet to DbSet... It is different brunches of EF evolution... In Code First case, i attempted use IDbSet, but DbContext can't have two similar properties - DbSet and IDbSet. The corresponding exception appear during initialization. — xtmq, Aug 21 '11 at 20:35
I would say we don't use context in unit tests. If tests requires to access context it should be integration test. http://stackoverflow.com/questions/6904139/fake-dbcontext-of-entity-framework-4-1-to-test/6904479#6904479 But the point of your question is very interesting. — Ladislav Mrnka, Aug 21 '11 at 21:07
It is very interesting answer, but not i have new couple of questions =) 1) Where i can read more about integration testing. I read about testing through FakeContexts on http://www.asp.net/mvc. And i had trusted in video lessons, but now i see that it has many issues... 2) Where i can write my linq to entity's queries? I had written it in my controller classes always... — xtmq, Aug 21 '11 at 21:25

score 4 · Answer 1 · answered Jun 14 '16 at 20:04

I ended up having two properties for each DbSet: one of type IQueryable, and one of type DbSet. The IQueryable property is defined in the interface, and it relays the calls to the concrete implementation (property of type DbSet), as follows:

// Exists in the interface
public IQueryable<AccountContact> AccountContacts
{ 
    get
    {
        return DbAccountContacts;
    }
    set
    {
        DbAccountContacts = (DbSet<AccountContact>)value; 
    }
}

// Exists only in the implementation
public DbSet<AccountContact> DbAccountContacts { get; set; }

Having this setup, I was able to get mocking to work correctly and could unit test the code.

This is definitely too late for the OP, but maybe this helps someone who is struggling with the same question, as I did.

Miguel Veloso · Accepted Answer · 2015-07-16T21:43:16.450

-2

I suggest you better keep DbSets and do INTEGRATION TESTING including the database.

Because, although passing a unit test with a mock of a DB could be somewhat usefull, you are going to be better off testing with real database (but it's not unit testing).

On the ClassInitialize erase the database and/or create the initial data for testing.

If you create an App.config file with a connection string you can have a separate test database, and if you are using EF Code First, you get it for free.

Best regards.

edited Jul 16 '15 at 21:43

answered Sep 01 '11 at 06:31

Miguel Veloso

1,055
10
16

Yes, i think you are right.. But how i can create and fill database in code? I can create domain objects in PrimaryDbContext. Can i create the database are containing this objects on local sql server through EF code first instruments automaticly? and use it in my tests? – xtmq Sep 03 '11 at 14:39
Yes, using EF Code First, the database can get created and populated the first time your app runs, check this tutorial: (http://www.asp.net/entity-framework/tutorials/creating-an-entity-framework-data-model-for-an-asp-net-mvc-application). One additional plus is that if you create an App.config file in the test project, you get a testing database for free, indepedent of your development database. I suggest you use SQL Server Compact 4.0. – Miguel Veloso Sep 07 '11 at 16:15
You're welcome!. Create another question and email me if you need further help. Best regards. – Miguel Veloso Sep 09 '11 at 20:22

Using IQueryable instead DbSet problem

2 Answers2

Linked