Unit Test SqlFunctions

Question

I have a repository that exposes IQueryable and a service handles specific queries and here a few methods that make use of DbFunctions. In order to be testable, i create a fake repository with static list of elements and inject it into the service. Problem is, since my service queries a List and does not make use of database, i get the error "This function can only be invoked from LINQ to Entities.".

Is there any easier way for testing this than creating a fake DbFunctions and QueryProvider?

Thanks in advance

That would only be [one of many required replacements](http://stackoverflow.com/questions/13332002/how-to-mock-the-limitations-of-entityframeworks-implementation-of-iqueryable/13352779#13352779). Do integration tests. Mocks will never be reliable OR require so much code (+ maintenance) that they become an application in their own right. You'll need unit tests for your unit test framework! — Gert Arnold, Jan 16 '14 at 14:16
Thanks Gert! It's dificult indeed to mock DbFunctions, i've been able to make a few that i needed, but like you said, it's though maintenance. — David Freire, Jan 22 '14 at 12:52

Anatoli Klamer · Answer 1 · 2018-05-19T12:47:45.967

I'm tried to implement dateDiff function, and it's works for me but we should think that in that case we test different functions and we are testing not real behaviour

 private class MySqlFunctions
    {
        [DbFunction("SqlServer", "DATEDIFF")]//EF will use this function
        public int? DateDiff(string datePartArg, DateTime startDate, DateTime endDate)
        {
            var subtract = startDate.Subtract(endDate);
            switch (datePartArg)
            {
                case "d":
                    return (int?)subtract.TotalDays;
                case "s":
                    return (int?)subtract.TotalSeconds; // unit test will use this one
            }
            throw new NotSupportedException("Method supports only s or d param");
        }
    }

Then in linq code

var sqlFunctions = new MySqlFunctions();

var result = matches.Average(s => sqlFunctions.DateDiff("s", s.MatchCreated, s.WaitingStarted);

score 0 · Answer 2 · answered Jan 16 '14 at 13:58

0

You can't reliably fake SQL due to the fact that LINQ to objects behaves differently in many cases than LINQ to SQL. For example, where (new [] { "asdf"}).Contains("ASDF") returns false in LINQ to objects, the same type of query in LINQ to SQL would return true. The best thing I've found to do is to separate the retrieval of data from the action on that data. Maybe create some kind of PersonManager that takes an IPersonRepository as a dependency. You can fake/mock IPersonRepository and use that to test that PersonManager does what it's supposed to do under various circumstances.

answered Jan 16 '14 at 13:58

Alex Dresko

5,179
3
37
57

Hi Alex, thanks for your reply. I just found someone with the same problem here: http://stackoverflow.com/questions/14883360/how-to-unit-test-getnewvalues-which-contains-entityfunctions-adddays-function The example you gave can be solved using the given solution, if you create a Contains method with the same signature on EntityFunctionsFake class and specify IgnoreCase on comparison. It may not be perfect for all situations, but at least i can test my requirements for now. What do you think? Cheers – David Freire Jan 16 '14 at 14:11
You can't use IgnoreCase in a LINQ query though. – Alex Dresko Jan 16 '14 at 14:53

score 0 · Answer 3 · answered Oct 07 '15 at 19:43

since i hit the same problem recently, and opted for a simpler solution, wanted to post it here.. this solution requires no Shims, Mocking, nothing expansive etc.

Pass a 'useDbFunctions' boolean flag to your method with default value as true.
When your live code executes, your query will use DbFunctions and everything will work. Due to the default value, callers need not worry about it.
When your unit tests invoke the method to test, they can pass useDbFunctions: false.
In your method, you can make use the flag to compose your IQueryable.. if useDbFunctions is true, use the DbFunctions to add the predicate to the queryable. if useDbFunctions is false, then skip the DbFunctions method call, and do an explicit C# equivalent solution.

This way, your unit tests will check almost 95% of your method in parity with live code. You still have the delta of "DbFunctions" vs. your equivalent code, but be diligent about it and the 95% will look like a lot of gain.

public SomeMethodWithDbFunctions(bool useDbFunctions = true)
{
    var queryable = db.Employees.Where(e=>e.Id==1); // without the DbFunctions

    if (useDbFunctions) // use the DbFunctions
    {
     queryable = queryable.Where(e=> 
     DbFunctions.AddSeconds(e.LoginTime, 3600) <= DateTime.Now);
    }  
    else
    {
      // do db-functions equivalent here using C# logic
      // this is what the unit test path will invoke
      queryable = queryable.Where(e=>e.LoginTime.AddSeconds(3600) < DateTime.Now);
    }                    

    var query = queryable.Select(); // do projections, sorting etc.
}

Unit tests will invoke the method as:

SomeMethodWithDbFunctions(useDbFunctions: false);

Because unit tests would have setup local DbContext entities, the C# logic/DateTime functions would work.

Unit Test SqlFunctions

3 Answers3

Linked