4

I am looking at creating a VB.NET 11 WPF MVVM application using Entity Framework 5 and Database First (Connecting to SQL Server 2008 R2).

I have chosen Database First, as I am migrating an existing solution to WPF MVVM, where the database already exists of course.

I'd like to begin using Dependency Injection so I can Unit Test as much of my code as possible.

I don't seem to be able to find a clear and concise walk-through of how to go about using Dependency Injection with EF DB-First, and in particular with vb.net. Although even a C# example would be fine I'm sure.

What I'd really like is a simple step by step guide explaining how to setup the solution, how to setup each part ready for Dependency Injection etc, but these seem hard to come by.

So far, I've created the Solution and it's Projects, as follows;

  • DBAccess - This houses nothing but my .edmx file, and a small mod to be able to supply the ConnectionString to the constructor.
  • DBControl - This houses the various classes which I use to provide a layer between my EDMX and my ViewModels. Specifically, I'm filling Complex Types (Which I have created using the designer) here for displaying "Friendlier" data via the UI, as well as converting these "Friendly" Complex Types to the mapped entities for saving / Updating. I have one class per table in my database. Each with two "FetchFriendlyRecords" methods (One accepts Filters) and an "AddUpdateFriendlyRecord" method. I have created an Interface for each class. Each class accepts a DbContext in it's constructor, and I'm simply passing my DBContext from the DBAccess Project.
  • MainUI - This houses my MVVM layers, and references each class in the DBControl Project in order to provide DataBinding etc.

I've seen suggested that, instead of spending time writing a complex solution to be able to unit test with EF, it's simpler to create a firm mock database with test data populated, and simply point the code at the mock database, rather than the live one. However, I'd prefer to be able to create an in memory solution that would run without any need to hit SQL Server at all.

Any help would be great, including telling me if I'm going about this all wrong!!

Update:

I have taken the solution provided by Paul Kirby below, and created a "Sort of" Repository Pattern I believe.

I created an interface;

Public Interface IFriendlyRepository(Of T)
        ReadOnly Property FriendlyRecords As ObservableCollection(Of T)
        Function GetFilteredFriendlyRecords(predicates As List(of Func(Of T, Boolean))) As ObservableCollection(Of T)
        Function AddEditFriendlyRecord(ByVal RecordToSave As T) As EntityException
        Sub SaveData()
End Interface

I then implemented this interface on a class by class basis;

Namespace Repositories
    Public Class clsCurrenciesRepository
        Implements Interfaces.IFriendlyRepository(Of CriticalPathDB.FriendlyCurrencies)

        Private _DBContext As CriticalPathEntities                          'The Data Context

        Public Sub New(ByVal Context As DbContext)   
            _DBContext = Context
        End Sub

        Public ReadOnly Property FriendlyRecords As ObservableCollection(Of FriendlyCurrencies) Implements Interfaces.IFriendlyRepository(Of CriticalPathDB.FriendlyCurrencies).FriendlyRecords
            Get
                ' We need to convert the results of a Linq to SQL stored procedure to a list,
                ' otherwise we get an error stating that the query cannot be enumerated twice!
                Dim Query = (From Currencies In _DBContext.Currencies.ToList
                             Group Join CreationUsers In _DBContext.Users.ToList
                             On Currencies.CreationUserCode Equals CreationUsers.User_Code Into JoinedCreationUsers = Group
                             From CreationUsers In JoinedCreationUsers.DefaultIfEmpty
                             Group Join UpdateUsers In _DBContext.Users.ToList
                             On Currencies.LastUpdateUserCode Equals UpdateUsers.User_Code Into JoinedUpdateUsers = Group
                             From UpdateUsers In JoinedUpdateUsers.DefaultIfEmpty
                             Where (Currencies.Deleted = False Or Currencies.Deleted Is Nothing)
                             Order By Currencies.NAME
                             Select New FriendlyCurrencies With {.Currency_Code = Currencies.Currency_Code,
                                                                .NAME = Currencies.NAME,
                                                                .Rate = Currencies.Rate,
                                                                .CreatedBy = If(Currencies.CreationUserCode Is Nothing, "", CreationUsers.First_Name & " " & CreationUsers.Last_Name),
                                                                .CreationDate = Currencies.CreationDate,
                                                                .CreationUserCode = Currencies.CreationUserCode,
                                                                .Deleted = Currencies.Deleted,
                                                                .LastUpdateDate = Currencies.LastUpdateDate,
                                                                .LastUpdatedBy = If(Currencies.LastUpdateUserCode Is Nothing, "", UpdateUsers.First_Name & " " & UpdateUsers.Last_Name),
                                                                .LastUpdateUserCode = Currencies.LastUpdateUserCode}).ToList

                Return New ObservableCollection(Of FriendlyCurrencies)(Query)
            End Get
        End Property

        Public Function GetFilteredFriendlyRecords(predicates As List(of Func(Of FriendlyCurrencies, Boolean))) As ObservableCollection(Of FriendlyCurrencies) Implements Interfaces.IFriendlyRepository(Of CriticalPathDB.FriendlyCurrencies).GetFilteredFriendlyRecords
            Dim ReturnQuery = FriendlyRecords.ToList

            For Each Predicate As Func(Of FriendlyCurrencies, Boolean) In predicates
                If Predicate IsNot Nothing Then
                    ReturnQuery = ReturnQuery.Where(Predicate).ToList
                End If
            Next
            Return New ObservableCollection(Of FriendlyCurrencies)(ReturnQuery)
        End Function

        Public Function AddEditFriendlyRecord(ByVal RecordToSave As FriendlyCurrencies) As EntityException Implements Interfaces.IFriendlyRepository(Of CriticalPathDB.FriendlyCurrencies).AddEditFriendlyRecord

            Dim dbCurrency As New Currency
            ' Check if this Staff Member Exists
            Dim query = From c In _DBContext.Currencies
                        Where c.Currency_Code = RecordToSave.Currency_Code
                        Select c

            ' If Asset exists, then edit.
            If query.Count > 0 Then
                dbCurrency = query.FirstOrDefault
            Else
                'Do Nothing
            End If

            dbCurrency.Currency_Code = RecordToSave.Currency_Code
            dbCurrency.NAME = RecordToSave.NAME
            dbCurrency.CreationDate = RecordToSave.CreationDate
            dbCurrency.CreationUserCode = RecordToSave.CreationUserCode
            dbCurrency.LastUpdateDate = RecordToSave.LastUpdateDate
            dbCurrency.LastUpdateUserCode = RecordToSave.LastUpdateUserCode
            dbCurrency.Deleted = RecordToSave.Deleted

            ' Save Asset Object to Database
            If query.Count > 0 Then
                ' If Asset exists, then edit.
                Try
                    '_dbContext.SaveChanges           'We could save here but it's generally bad practice
                Catch ex As EntityException
                    Return ex
                End Try
            Else
                Try
                    _DBContext.Currencies.Add(dbCurrency)
                    '_dbContext.SaveChanges           'We could save here but it's generally bad practice
                Catch ex As EntityException
                    Return ex
                End Try
            End If
            Return Nothing
        End Function

        Public Sub SaveData() Implements Interfaces.IFriendlyRepository(Of CriticalPathDB.FriendlyCurrencies).SaveData
            _DBContext.SaveChanges()
        End Sub
    End Class
End Namespace

I used constructor injection to insert the dbContext into the class.

I had hoped to be able to mock up a fake dbContext using my existing context and the "Effort" Unit Testing Tool.

However, I don't seem to be able to get this to work.

In the interim, in my Unit Test Project, I am dropping (If it already exists) and creating an empty test database, with the SQLCMD command, using the same schema as my live database.

I then create a dbContext referencing the Test Database, populate it with test data, and test against this.

As a note, I will be refactoring my "Add/Edit" method to work with an actual base Entity, rather than my "Friendly" complex version, this was the simplest method at the time.

Improve this question

1 Answer 1

Reset to default
6

If you're working DB-first, here's what I would suggest.

  1. Open your .edmx file, right click on any blank space and choose "Add Code Generation Item"
  2. In the "Online Templates" area, search for "EF 5.x DbContext Generator for VB".
  3. Give the .tt file a name, hit add. This will change the way your .edmx file generates the backing code so that your entities are all POCO, which simplifies testing overall by keeping your main logic disconnected from EF.

After you've got that done, you probably want to look into something like the Unit of Work pattern. Here's a quick code example, I'll explain it after.

public interface IUnitOfWork
{
    IDbSet<Location> Locations { get; }
    void Commit();
}

public class EFUnitOfWork : IUnitOfWork
{
    private readonly YourGeneratedDbContext _context;

    public EFUnitOfWork(string connectionString)
    {
        _context = new YourGeneratedDbContext();
    }

    public IDbSet<Location> Locations
    {
        get { return _context.Locations; }
    }

    public void Commit()
    {
        _context.SaveChanges();
    }
}

This is a basic unit of work that exposes some list of Locations as an example (sorry that it's in C# but I don't know VB well).

Notice that it is exposing IDbSet objects - this is where the magic comes in. If in your DBAccess project, you use this unit of work or a repository pattern to hide EF, and because it implements an interface and is returning IDbSet objects, anywhere that needs your data can have this IUnitOfWork constructor injected with DI, and replaced with a mocked version that returns mock IDbSet objects (they're just IQueryables in the end) when you need to unit test.

You may find that with the POCO generation in that new template, you can even do away with a lot of the work you're doing in your DBControl project.

Anyway that's just some basic stuff in terms of positioning your project for optimal unit testing and DI.

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

4 Comments

Thanks very much indeed for that... I've added the code generation, and deleted the existing .tt file (Which was of course generating errors)... I'll have a play now and come back to you... Thanks again!
Just a quick question... Do I then need to add IDbSet's for all my entities to this one class file? Including the functions to get my "Friendly" data? What I'd like to be able to do is test these functions against a mock data source.
That's typically what I do, yes. Your unit of work should compromise all of the types of objects you might need to use in one particular high-level operation. Of course, you may have multiple different units of work too. As an example, perhaps you have a unit of work for an operation that allows you to search for users by address (so you need Location and User objects), and another for an operation that inserts users and their permissions (User and Permission objects). It mostly depends on the context of what you're doing.
Thanks for that again... I got your solution working... and now I've evolved it to (just about) include usage of "Effort"... For mocking my database. Once I've finalised it, I'll post my solution, as another answer!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.