0

Here's the background: I built a new WCF service which uses EF5 and was targeting .Net Framework 4.5. I've used code-first against an existing large legacy database. All was working nicely until I came to deploy the service to a test server, when I discovered the test server (and therefore the production server since they are the same build) was not capable of supporting Framework 4.5.

So, I downgraded the project to target Framework 4.0. After re-coding all my entity mappings (to avoid using code in the DataAnnotations.Schema namespace specific to 4.5), I had this working again locally. However, after deploying to the server I was hitting the error described here: Can anyone spot why I keep getting this error testing the EF 5 beta.

After doing as suggested - uninstalling and re-installing EF5 via NuGet, I am now getting a timeout error when trying to test the service.

Specifically the timeout occurs when attempting to execute the following Linq:

var games = from m in _db.Members
        join s in _db.UkSyndicates
            on m.MemberId equals s.MemberId
        where m.PaymentMethod == "Credit/Debit Card"
        && EntityFunctions.TruncateTime(s.NextChargeDate) <=  EntityFunctions.TruncateTime(DateTime.Now)
        && !string.IsNullOrEmpty(m.AibCustomerReference)
        && (m.StatusId == 105 || m.StatusId == 113)
        select new BillingItem
        {
            Id = s.Id,
            SyndicateId = s.SyndicateId,
            MemberId = s.MemberId,
            LastDrawId = s.LastDrawId,
            LastPaidGame = s.LastPaidGame,
            NextChargeDate = s.NextChargeDate,
            Protected = s.Protected,
            PaymentFrequency = (int)(s.PaymentFrequency*4),
            CurrencyCode = m.CurrencyCode,
            AibCustomerReference = m.AibCustomerReference,
            WeeklyFee = (from f in _db.GameFees where f.FeeName == "UK_LOTTO_FEE" && f.CurrencyCode == m.CurrencyCode select f.Amount).FirstOrDefault(),
            GameCode = game,
            PostCode = m.PostCode,
            CountryCode = m.CountryCode,
            EmailAddress = m.Email
        };

There may well be a more efficient way to write this query - I'm fairly inexperienced with Linq and EF, but I would stress this did NOT time-out prior to the uninstall and re-install of EF. In fact it returned data fairly quickly.

I can't see any reason this would timeout after the re-install of EF, nothing in the code has changed, but need a solution to this fast!

Thanks.

To clarify the error I'm getting, it's at the point that EF tries to execute the above query, I get an EntityCommandExecutionException thrown. The exception message is "An error occurred while executing the command definition. See the inner exception for details." The inner exception is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

The stack trace is below:

at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues) at System.Data.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) at System.Data.Objects.ObjectQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() at System.Data.Entity.Internal.Linq.InternalQuery1.GetEnumerator() at System.Data.Entity.Infrastructure.DbQuery1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator() at RepeatBillingService.Repositories.BillingRepository.GetMembersForGame(String game, List1& billingQ) in p:\Projects\BigFatLottos\RepeatBillingService\RepeatBillingService\Repositories\BillingRepository.cs:line 441 at RepeatBillingService.Repositories.BillingRepository.GetMemberGamesForBilling() in p:\Projects\BigFatLottos\RepeatBillingService\RepeatBillingService\Repositories\BillingRepository.cs:line 24 at RepeatBillingService.RepeatBillingService.RunRepeatBilling() in p:\Projects\BigFatLottos\RepeatBillingService\RepeatBillingService\RepeatBillingService.svc.cs:line 51 at SyncInvokeRunRepeatBilling(Object , Object[] , Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)

Community
  • 1
  • 1
PeteM
  • 343
  • 3
  • 10
  • Why coode-first against an existing database? Surely the natural choice would be database first? It seems odd to me, though this is not the first time I've seen that decision made... – Paul Fleming Oct 13 '12 at 19:18
  • What is timing out? The database or the service? – Paul Fleming Oct 13 '12 at 19:20
  • @flem Thanks for your response. The database is a huge legacy thing, and full of poorly-named columns etc. I only need about 15 tables for this particular job, so wanted to write "clean" entities and map those to the tables required. So code-first seemed to be the way to go - but there may well be a better way, as I said I'm quite new to EF. – PeteM Oct 13 '12 at 19:37
  • I can step through the service in debug, and it times out when it comes to execute the linq posted. In the last few minutes I have used trial and error, simplifying the query, and it returns data successfully (and instantly) when most of the conditions are removed. So it seems it's a case of putting back the conditions one by one to identify the culprit. But what I don't understand is why this was fine prior to re-installing EF. – PeteM Oct 13 '12 at 19:40
  • Further to the above, the problem seems to lie with the use of **EntityFunctions.TruncateTime**. Is this a known issue? Is that method only supported in Framework 4.5? After re-targeting the project, I still get intellisense on it, and the project builds and runs so wouldn't expect it to cause a problem. – PeteM Oct 13 '12 at 19:52
  • @flem - using BbContext/CodeFirst with an existing database is perfectly valid scenario. In fact using EF Power Tools you can reverse engineer database for your code first app. This post is also worth reading http://blog.oneunicorn.com/2012/02/26/dont-use-code-first-by-mistake/ – Pawel Oct 13 '12 at 20:28
  • @PeteM truncate function existed in .NET Framework 4 (http://msdn.microsoft.com/en-us/library/dd395596(v=vs.100).aspx). It's hard to tell what's going on in this case without more details. What kind of timeout do you get? Where is it coming from - a database? Show the exception and the stack trace. Have you tried executing the Sql Query generated by EF in Sql Server Management Studio (I am assuming you are using SqlServer) and see how long it takes? EF sometimes create bad queries. There were some improvements in this area in .NET Framework 4.5 - did you compare queries generated for 4 vs 4.5? – Pawel Oct 13 '12 at 20:34
  • @Pawel thanks for your comments. I did in fact use the Power Tools reverse engineer to get the mappings for my DB, then amended to use my "clean" property names. Yes I'm using SqlServer - I have tried to pull out the sql but it always contains some linq-generated variables, so difficult to run it. Maybe I'm doing something wrong there, not sure the best way to get the sql out. – PeteM Oct 14 '12 at 00:11
  • I have narrowed the problem down to the specific field within the query that I'm applying TruncateTime to (s.NextChargeDate), so in fact TruncateTime itself is not the problem. The NextChargeDate field is a nullable DateTime, and it contains a lot of null values in the database. In fact I nulled out most of the values, to leave a few records for testing purposes. I thought perhaps this was causing the problem, so added a `s.NextChargeDate != null` clause, but this makes no difference. It seems any query including that field causes the timeout, if I remove it everything is fine. – PeteM Oct 14 '12 at 00:13
  • @Pawel, I've updated the post with the full exception details and stack trace. – PeteM Oct 14 '12 at 00:23
  • Pete, I don't know what could be causing this difference. Looking at the query and TruncateTime, it doesn't ring a bell. Could you obtain the SQL query that is generated for this LINQ query with both .NET 4.0 and .NET 4.5 and provide them here? You can call ToString on the query to get the SQL. (BTW, the .NET 4.0 version of EF 5 places the schema data annotations places in the same namespace as .NET 4.5, so once you reinstall EF5 your code doesn't need to change between the two. Also, if you have a navigation property between Members and UkSyndicates, you should not need using join.) – divega Oct 14 '12 at 20:43
  • I had missed the comment about the nullable field. In .NET 4.5 we made a few improvements to the queries we produce for comparisons between nullable elements. Still it is difficult to guess exactly what is going on. Please post the SQL that is generated in both versions of .NET. – divega Oct 14 '12 at 21:01

1 Answers1

1

I got to the bottom of this, and apologise as the whole question was a bit of a red herring. I'd had a SSMS session open in the background where I was tweaking the data for testing, and had been updating the "NextChargeDate" values. When I came to close down the session, I got "there are uncommitted transactions, do you want to commit now" which started alarm bells ringing.

Sure enough on restarting the WCF service, everything worked perfectly. So my timeout was occurring because of uncommitted updates on that field in the DB - EF obviously couldn't read the data successfully until the updates were committed.

So my apologies for wasting your time with this question, just a simple oversight while working long hours under pressure to get a new system live.

Thanks anyway for your responses.

PeteM
  • 343
  • 3
  • 10