1

I got a huge LINQ-statement with multiple joins with multiple conditions. I need to use database functions (DatePart, DiffDays) in one of the joins. I use Moq for Unit Testing. As those DBFunctions are only invokable via static methods I can't mock these methods. If I call them via SqlFunctions or EntityFunctions I get a NotSupportedException ("This function can only be invoked from LINQ to Entities."), but my application works. If I use System.Data.Entity.DbFunction it also works in my application, but not in my unit-test.

I shortened the LINQ-Statement, actually it contains a lot more joins: 

 var resultQuery = from t1 in ctx.Table1.AsNoTracking()    
                              join t2 in ctx.Table2 on new
                              {
//works for application, but not in Unit-Test, firstMonday is a predefined DateTime-variable
                                  z3 = (int)EntityFunctions.DiffDays(firstMonday, t1.Date)%7,
//works only in Application, but not in Unit-Test
                                  z4 = SqlFunctions.DatePart("wk", t2.Date)
                              } equals new
                              {
                                  z3 = (int)t2.Day,         
                                  z4 = SqlFunctions.DatePart("wk", t2.BeginDate)
                              }  into tGroup

                              from x in tGroup.DefaultIfEmpty()
                              where
[...]
//Class DataTransferObjekt defined to hold attributes of both tables

                    select new DataTransferObject
                    {
                        Id = t1.Id,
                        Date = t1.Date,
                        [...]
                        T2Id = t2.ID
                    };

I tried a lot and did a lot research. Best approaches I found here: EntityFunctions.TruncateTime and unit tests

But I was unable to transfer the offered solutions to my special case as my condition is part of a join and the solution using System.Data.Entity.DbFunction offered in the second answer also didn't work for the Unit-Test as mentioned before.

Any help would be appreciated, I'm still quite new in Moq and LINQ also!

L. Schmidt
  • 31
  • 3
  • I don't know which C#-methods could be appropriate, but I will google for it. Thanks! I just fear, they won't be supported by LINQ. This was also the problem, when I tried to wrap the static methods and call the non-static wrapping method instead. – L. Schmidt Nov 13 '17 at 13:08
  • I tried it for Diffdays, I got now an exception in my application and it works for unit-test: Exception message: LINQ to Entities does not recognize the method 'System.TimeSpan Subtract(System.DateTime)' method, and this method cannot be translated into a store expression. – L. Schmidt Nov 13 '17 at 13:20
  • 1
    Are you trying to unit test you Linq queries that actually run against a DB? That won't be a useful test, in memory queries wrapped in an IQueryable don't behave like DB queries so it proves nothing. If you just care about the result for your test put you query in a method. This is not something suitable for a unit test. – Mant101 Nov 13 '17 at 16:17
  • I have mocked the context for unit testing and I want to test if the linq query is correct as it contains of many conditions and joins. – L. Schmidt Nov 14 '17 at 07:24
  • 1
    You can't test the Linq query is correct against an in memory query because Linq behaves differently depending on the provider. You test won't prove your query is correct against your DB which is what you care about. – Mant101 Nov 14 '17 at 09:26
  • Ok, thank you, though that's not what I wanted to hear ;-) – L. Schmidt Nov 14 '17 at 09:38
  • If you think about it the Linq is replacing SQL, how would you test SQL? I've seen in memory DBs you can use to test EF with although I haven't tried that myself they may be worth looking at (one is here https://github.com/tamasflamich/effort and I think EF Core has one) – Mant101 Nov 14 '17 at 10:00

0 Answers0