EntityFramework, SQL Server & FXServer

[Zoull]

I’ve recently started trying to develop my own GTA RP Server. I’ve been able to accomplish some things that don’t require a database and how to translate LUA into C#. But I’m getting to the point now where I need to get my server connecting to a database so that I can store information off. I’m trying to use EntityFramework to connect to a SQL Server Database using Code First.

My setup right now is very simple. My database context is just this right now:

public partial class XeroRpContext : DbContext, IXeroRpContext
{
    public XeroRpContext()
    {

    }

    public DbSet<Player> Player { get; set; }
}

The Player Entity is the first table that I’m trying to create to be able to store Player information to be able to start setting up a whitelist and it’s just a class with properties. If I do anything that tries to open the database, then it throws exceptions saying that it can’t find the libraries Mono.Data.Tds and System.Data.OracleClient

 Exception loading assembly Mono.Data.Tds: System.IO.FileNotFoundException: Unable to find the specified file.
  
 Server stack trace: 
   at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR (System.Int32 errorCode) [0x0000a] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at (wrapper cominterop) CitizenFX.Core.IScriptHost:OpenHostFile (string)
   at (wrapper cominterop-invoke) CitizenFX.Core.IScriptHost:OpenHostFile (string)
   at CitizenFX.Core.MonoScriptRuntime+WrapScriptHost.OpenHostFile (System.String fileName) [0x00000] in C:\gl\builds\4ff63adb\0\cfx\fivem\code\client\clrcore\MonoScriptRuntime.cs:254 
   at (wrapper remoting-invoke-with-check) CitizenFX.Core.MonoScriptRuntime+WrapScriptHost:OpenHostFile (string)
   at (wrapper xdomain-dispatch) CitizenFX.Core.MonoScriptRuntime+WrapScriptHost:OpenHostFile (object,byte[]&,byte[]&,string)
 
 Exception rethrown at [0]: 
   at (wrapper xdomain-invoke) CitizenFX.Core.MonoScriptRuntime+WrapScriptHost:OpenHostFile (string)
   at CitizenFX.Core.InternalManager.LoadAssembly (System.String name) [0x00000] in C:\gl\builds\4ff63adb\0\cfx\fivem\code\client\clrcore\InternalManager.cs:112 
 Exception loading assembly Mono.Data.Tds: System.IO.FileNotFoundException: Unable to find the specified file.
  
 Server stack trace: 
   at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR (System.Int32 errorCode) [0x0000a] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at (wrapper cominterop) CitizenFX.Core.IScriptHost:OpenHostFile (string)
   at (wrapper cominterop-invoke) CitizenFX.Core.IScriptHost:OpenHostFile (string)
   at CitizenFX.Core.MonoScriptRuntime+WrapScriptHost.OpenHostFile (System.String fileName) [0x00000] in C:\gl\builds\4ff63adb\0\cfx\fivem\code\client\clrcore\MonoScriptRuntime.cs:254 
   at (wrapper remoting-invoke-with-check) CitizenFX.Core.MonoScriptRuntime+WrapScriptHost:OpenHostFile (string)
   at (wrapper xdomain-dispatch) CitizenFX.Core.MonoScriptRuntime+WrapScriptHost:OpenHostFile (object,byte[]&,byte[]&,string)
 
 Exception rethrown at [0]: 
   at (wrapper xdomain-invoke) CitizenFX.Core.MonoScriptRuntime+WrapScriptHost:OpenHostFile (string)
   at CitizenFX.Core.InternalManager.LoadAssembly (System.String name) [0x00000] in C:\gl\builds\4ff63adb\0\cfx\fivem\code\client\clrcore\InternalManager.cs:112 
 Error invoking callback for event onResourceStart: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.TypeLoadException: Could not load type of field 'System.Data.SqlClient.SqlConnection:sqlConnectionPools' (1) due to: Could not load file or assembly 'Mono.Data.Tds, Version=4.0.0.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756' or one of its dependencies. assembly:Mono.Data.Tds, Version=4.0.0.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756 type:<unknown type> member:<none>
   at System.Data.Entity.Internal.LazyInternalConnection.Initialize () [0x000bd] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.LazyInternalConnection.get_ProviderName () [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.LazyInternalContext.get_ProviderName () [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.DefaultModelCacheKeyFactory.Create (System.Data.Entity.DbContext context) [0x0002b] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.LazyInternalContext.InitializeContext () [0x0009b] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.InternalContext.Initialize () [0x00005] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType (System.Type entityType) [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.Linq.InternalSet`1[TEntity].Initialize () [0x0000e] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.Linq.InternalSet`1[TEntity].get_InternalContext () [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.Linq.InternalSet`1[TEntity].ActOnSet (System.Action action, System.Data.Entity.EntityState newState, System.Object entity, System.String methodName) [0x0002a] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.Linq.InternalSet`1[TEntity].Add (System.Object entity) [0x00014] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.DbSet`1[TEntity].Add (TEntity entity) [0x00017] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at XeroRP.Whitelist.General.Server.Script.OnResourceStart (System.String resourceName) [0x0005e] in <5f0bc482949f497ca366f015ad629ad0>:0 
   at (wrapper managed-to-native) System.Reflection.MonoMethod:InternalInvoke (System.Reflection.MonoMethod,object,object[],System.Exception&)
   at System.Reflection.MonoMethod.Invoke (System.Object obj, System.Reflection.BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00032] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
    --- End of inner exception stack trace ---
   at System.Reflection.MonoMethod.Invoke (System.Object obj, System.Reflection.BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00048] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at System.Reflection.MethodBase.Invoke (System.Object obj, System.Object[] parameters) [0x00000] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at System.Delegate.DynamicInvokeImpl (System.Object[] args) [0x000e7] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at System.MulticastDelegate.DynamicInvokeImpl (System.Object[] args) [0x00008] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at System.Delegate.DynamicInvoke (System.Object[] args) [0x00000] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at CitizenFX.Core.EventHandlerEntry+<Invoke>d__5.MoveNext () [0x00064] in C:\gl\builds\4ff63adb\0\cfx\fivem\code\client\clrcore\EventHandlerDictionary.cs:85 

I Googled and found these libraries, but if I add them, then it complains about a ‘Password’ field not being set (I also worry that finding these libraries randomly on Google shouldn’t be necessary)

 Error invoking callback for event onResourceStart: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.MissingFieldException: Field 'Mono.Data.Tds.Protocol.TdsConnectionParameters.Password' not found.
   at System.Data.SqlClient.SqlConnection.SetConnectionString (System.String connectionString) [0x0015c] in <c660f5a12cac430aa34fafb29a22e588>:0 
   at System.Data.SqlClient.SqlConnection.set_ConnectionString (System.String value) [0x00014] in <c660f5a12cac430aa34fafb29a22e588>:0 
   at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.<SetConnectionString>b__18 (System.Data.Common.DbConnection t, System.Data.Entity.Infrastructure.Interception.DbConnectionPropertyInterceptionContext`1[TValue] c) [0x00007] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1[TInterceptor].Dispatch[TTarget,TInterceptionContext] (TTarget target, System.Action`2[T1,T2] operation, TInterceptionContext interceptionContext, System.Action`3[T1,T2,T3] executing, System.Action`3[T1,T2,T3] executed) [0x0000f] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.SetConnectionString (System.Data.Common.DbConnection connection, System.Data.Entity.Infrastructure.Interception.DbConnectionPropertyInterceptionContext`1[TValue] interceptionContext) [0x00042] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Infrastructure.SqlConnectionFactory.CreateConnection (System.String nameOrConnectionString) [0x00098] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.LazyInternalConnection.Initialize () [0x000bd] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.LazyInternalConnection.get_ProviderName () [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.LazyInternalContext.get_ProviderName () [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.DefaultModelCacheKeyFactory.Create (System.Data.Entity.DbContext context) [0x0002b] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.LazyInternalContext.InitializeContext () [0x0009b] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.InternalContext.Initialize () [0x00005] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType (System.Type entityType) [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.Linq.InternalSet`1[TEntity].Initialize () [0x0000e] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.Linq.InternalSet`1[TEntity].get_InternalContext () [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.Linq.InternalSet`1[TEntity].ActOnSet (System.Action action, System.Data.Entity.EntityState newState, System.Object entity, System.String methodName) [0x0002a] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.Linq.InternalSet`1[TEntity].Add (System.Object entity) [0x00014] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.DbSet`1[TEntity].Add (TEntity entity) [0x00017] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at XeroRP.Whitelist.General.Server.Script.OnResourceStart (System.String resourceName) [0x0005e] in <5f0bc482949f497ca366f015ad629ad0>:0 
   at (wrapper managed-to-native) System.Reflection.MonoMethod:InternalInvoke (System.Reflection.MonoMethod,object,object[],System.Exception&)
   at System.Reflection.MonoMethod.Invoke (System.Object obj, System.Reflection.BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00032] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
    --- End of inner exception stack trace ---
   at System.Reflection.MonoMethod.Invoke (System.Object obj, System.Reflection.BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00048] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at System.Reflection.MethodBase.Invoke (System.Object obj, System.Object[] parameters) [0x00000] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at System.Delegate.DynamicInvokeImpl (System.Object[] args) [0x000e7] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at System.MulticastDelegate.DynamicInvokeImpl (System.Object[] args) [0x00008] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at System.Delegate.DynamicInvoke (System.Object[] args) [0x00000] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at CitizenFX.Core.EventHandlerEntry+<Invoke>d__5.MoveNext () [0x00064] in C:\gl\builds\4ff63adb\0\cfx\fivem\code\client\clrcore\EventHandlerDictionary.cs:85 

I’ve also tried manually specifying the connection string, without and with the libraries mentioned above

public XeroRpContext()
{
    Database.Connection.ConnectionString = "data source=xxx.xxx.xxx.xxx;database=dbname;user id=user_name;password=password;";
}

But it throws the same exceptions as above. I’ve also tried calling the DbContext constructor and passing in the connection string name

public XeroRpContext() : base("name=XeroRpContext")
{

}

But it throws an exception saying that it can’t find a connection string with the specified name

 Error invoking callback for event onResourceStart: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: No connection string named 'XeroRpContext' could be found in the application config file.
   at System.Data.Entity.Internal.LazyInternalConnection.get_ConnectionHasModel () [0x00072] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.LazyInternalContext.InitializeContext () [0x0006f] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.InternalContext.Initialize () [0x00005] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType (System.Type entityType) [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.Linq.InternalSet`1[TEntity].Initialize () [0x0000e] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.Linq.InternalSet`1[TEntity].get_InternalContext () [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.Linq.InternalSet`1[TEntity].ActOnSet (System.Action action, System.Data.Entity.EntityState newState, System.Object entity, System.String methodName) [0x0002a] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.Internal.Linq.InternalSet`1[TEntity].Add (System.Object entity) [0x00014] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at System.Data.Entity.DbSet`1[TEntity].Add (TEntity entity) [0x00017] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0 
   at XeroRP.Whitelist.General.Server.Script.OnResourceStart (System.String resourceName) [0x0005e] in <d8294710617648be984c00156502ac43>:0 
   at (wrapper managed-to-native) System.Reflection.MonoMethod:InternalInvoke (System.Reflection.MonoMethod,object,object[],System.Exception&)
   at System.Reflection.MonoMethod.Invoke (System.Object obj, System.Reflection.BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00032] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
    --- End of inner exception stack trace ---
   at System.Reflection.MonoMethod.Invoke (System.Object obj, System.Reflection.BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00048] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at System.Reflection.MethodBase.Invoke (System.Object obj, System.Object[] parameters) [0x00000] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at System.Delegate.DynamicInvokeImpl (System.Object[] args) [0x000e7] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at System.MulticastDelegate.DynamicInvokeImpl (System.Object[] args) [0x00008] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at System.Delegate.DynamicInvoke (System.Object[] args) [0x00000] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
   at CitizenFX.Core.EventHandlerEntry+<Invoke>d__5.MoveNext () [0x00064] in C:\gl\builds\4ff63adb\0\cfx\fivem\code\client\clrcore\EventHandlerDictionary.cs:85 

I’ve tried putting the App.config everywhere I can think of in my servers folders, but I don’t think it would matter since I tried specifying the connection string manually. Am I missing something? Can I actually connect to a SQL Server with EntityFramework? Or do I have to use a MySQL Server and the MySQL Connector? Can anyone help me and shed some light on whatever it is that I’m missing?

Sorry for the long post and thank you for your time and patience. Also let me know if there’s any more information that I should provide.

[tungsten]

@Zoull

Can I actually connect to a SQL Server with EntityFramework?

Probably not, unless there is a fully standalone client library that does not depend on special class libraries in the runtime FCL.

[Zoull]

The only thing that leads me to believe that it might be possible, is this post here: [FXServer] EntityFramework + FXServer?
That talks about getting EntityFramework working with a MySQL database which also requires these libraries

MySql.Data
MySql.Data.Entity.EF6

With EntityFramework to get EntityFramework working with a MySQL database. So I feel like it might be possible, but that I might just be missing something. Like maybe I need to have Mono installed on the development and hosting machines.
Could that be a possibility? Or is it because those MySQL libraries are used that allows for EntityFramework to be used?

[iridium]

@Zoull

Or is it because those MySQL libraries are used that allows for EntityFramework to be used?

yes

[Zoull]

So, I looked into the MySQL Async Library and converted it for SQL Server and actually got it working by finding and downloading these libraries:

Mono.Data.Tds
I18N
I18N.West

If anyone would like me to put that code up on github and make a release post, just let me know.
Now I’m going to try and use this knowledge to get my code first version for SQL Server working.

Thank you to everyone that provided help of any kind.

[Zoull]

I also got code first with SQL Server working.
I had to use another library:

System.Data.OracleClient

To get it working. And to fix the issue with it saying that the app config doesn’t contain a connection string for ‘XeroRpContext’ (the name of the connection string), I had to add my connection string to the machine.config for mono.

Again, thank you to everyone who provided help of any kind.

[Josh_Smith]

Can you post the code where you got this working with EF? Would like to see exactly how you did it, trying to do a similar thing.

[Zoull]

The code first implementation or the async implementation?

[Josh_Smith]

Code first implementation with the correct libraries would be amazing. I am trying to accomplish the same thing. I am familiar with how to make this work using MSSQL and everything like that but with FiveM restrictions, that is not exactly possible. Would like to use the source as a reference to get started with my project.

[Zoull]

I only use the EntityFramework library for the code first implementation. I do use the EntityFramework.Cache library for an extra layer of caching, but that library shouldn’t be needed. You do need all 4 of the libraries stated above in the resource folder for any scripts that use EntityFramework with a SQL Server Database in order for FiveM to load the libraries properly.

I’m currently in the middle of redoing my server that contains my GitLab instance that contains all of my source code. But as soon as I’m done, I’ll commit the relevant projects to my GitHub publicly and start a release topic and put the link to that here.

Please keep in mind that I do my code first implementation a little different and a little more verbose than a lot of people do, so it might be a little weird to look at, at first.

Edit: Also, it uses a Dependency Injection Framework that I slimmed down from the System.Web namespace and converted to work with normal desktop applications and it uses the Unity Inversion of Control Container and a ServiceFactory to be able to use the Dependency Injection in the scripts. So if you don’t need Dependency Injection, you may have to modify things a bit to get them working the way that you would like.

[Zoull]

Sorry it took me so long to get everything up, but I finally got it. You can find the release post over here:

[Release] SQL Server Code First

SQL Server Code First This allows you to use SQL Server with FX Server. This is for C# only. All of the setup and instructions are on GitHub. If you have any suggestions or improvements or need any help, just post them up here or email me at the email listed on GitHub and I’ll try and accommodate you. Current Features Completely Code First No working with SQL code Shouldn’t even have to touch the database except to troubleshoot issues Dependency Injection and Inversion of Control Change L…

If you have any questions, need any help or want to make any suggestions, please post them on that release thread.

Good luck with your project!

[Josh_Smith]

Hey Zoull,

What you posted has been great and I’ve written quite a bit of my framework at this point. One errors that I seem to run into consistently is related to DateTime fields. If you have a DateTime property on an entity, when you try to set that property you will receive the following error:

Exception thrown by a task: System.AggregateException: One or more errors occurred. ---> System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string.
  at System.Data.SqlClient.SqlConnection.ErrorHandler (System.Object sender, Mono.Data.Tds.Protocol.TdsInternalErrorMessageEventArgs e) [0x00057] in <c660f5a12cac430aa34fafb29a22e588>:0
  at Mono.Data.Tds.Protocol.Tds.OnTdsErrorMessage (Mono.Data.Tds.Protocol.TdsInternalErrorMessageEventArgs e) [0x0000b] in <b47bf4b6efd8474e81b79858fb929410>:0
  at Mono.Data.Tds.Protocol.Tds.ProcessMessage (Mono.Data.Tds.Protocol.TdsPacketSubType subType) [0x00102] in <b47bf4b6efd8474e81b79858fb929410>:0
  at Mono.Data.Tds.Protocol.Tds.ProcessSubPacket () [0x0012a] in <b47bf4b6efd8474e81b79858fb929410>:0
  at Mono.Data.Tds.Protocol.Tds.NextResult () [0x0004a] in <b47bf4b6efd8474e81b79858fb929410>:0
  at System.Data.SqlClient.SqlDataReader.NextResult () [0x00042] in <c660f5a12cac430aa34fafb29a22e588>:0
  at System.Data.SqlClient.SqlDataReader..ctor (System.Data.SqlClient.SqlCommand command) [0x00019] in <c660f5a12cac430aa34fafb29a22e588>:0
  at (wrapper remoting-invoke-with-check) System.Data.SqlClient.SqlDataReader:.ctor (System.Data.SqlClient.SqlCommand)
  at System.Data.SqlClient.SqlCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x00068] in <c660f5a12cac430aa34fafb29a22e588>:0
  at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader (System.Data.CommandBehavior behavior) [0x00000] in <c660f5a12cac430aa34fafb29a22e588>:0
  at System.Data.Common.DbCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x00000] in <c660f5a12cac430aa34fafb29a22e588>:0
  at (wrapper remoting-invoke-with-check) System.Data.Common.DbCommand:ExecuteReader (System.Data.CommandBehavior)
  at EFCache.CachingCommand.ExecuteDbDataReader (System.Data.CommandBehavior behavior) [0x00008] in <1802f9202cc445d08c2ad065e84fb563>:0
  at System.Data.Common.DbCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x00000] in <c660f5a12cac430aa34fafb29a22e588>:0
  at (wrapper remoting-invoke-with-check) System.Data.Common.DbCommand:ExecuteReader (System.Data.CommandBehavior)
  at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c (System.Data.Common.DbCommand t, System.Data.Entity.Infrastructure.Interception.DbCommandInterceptionContext`1[TResult] c) [0x00007] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1[TInterceptor].Dispatch[TTarget,TInterceptionContext,TResult] (TTarget target, System.Func`3[T1,T2,TResult] operation, TInterceptionContext interceptionContext, System.Action`3[T1,T2,T3] executing, System.Action`3[T1,T2,T3] executed) [0x0000f] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader (System.Data.Common.DbCommand command, System.Data.Entity.Infrastructure.Interception.DbCommandInterceptionContext interceptionContext) [0x00042] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader (System.Data.CommandBehavior behavior) [0x00046] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Common.DbCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x00000] in <c660f5a12cac430aa34fafb29a22e588>:0
  at (wrapper remoting-invoke-with-check) System.Data.Common.DbCommand:ExecuteReader (System.Data.CommandBehavior)
  at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute (System.Collections.Generic.Dictionary`2[TKey,TValue] identifierValues, System.Collections.Generic.List`1[T] generatedValues) [0x0007e] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update () [0x0002b] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
   --- End of inner exception stack trace ---
  at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update () [0x00080] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.<Update>b__2 (System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator ut) [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T] (T noChangesResult, System.Func`2[T,TResult] updateFunction) [0x00063] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update () [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesToStore>b__35 () [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T] (System.Func`1[TResult] func, System.Data.Entity.Infrastructure.IDbExecutionStrategy executionStrategy, System.Boolean startLocalTransaction, System.Boolean releaseConnectionOnSuccess) [0x0008a] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore (System.Data.Entity.Core.Objects.SaveOptions options, System.Data.Entity.Infrastructure.IDbExecutionStrategy executionStrategy, System.Boolean startLocalTransaction) [0x0002e] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.Objects.ObjectContext+<>c__DisplayClass2a.<SaveChangesInternal>b__27 () [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult] (System.Func`1[TResult] operation) [0x00036] in <5c51bb66ce2c412d8224b39ad2626fbe>:0
  at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal (System.Data.Entity.Core.Objects.SaveOptions options, System.Boolean executeInExistingTransaction) [0x00072] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges (System.Data.Entity.Core.Objects.SaveOptions options) [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Internal.InternalContext.SaveChanges () [0x0004d] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
   --- End of inner exception stack trace ---
  at System.Data.Entity.Internal.InternalContext.SaveChanges () [0x0005f] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Internal.LazyInternalContext.SaveChanges () [0x00008] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.DbContext.SaveChanges () [0x00006] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at GreenLeafGaming.Gta.Services.Data.Context.DatabaseContext.SaveChanges () [0x00062] in <9c7aa4bdb7404628b0859d010ed00cab>:0
  at GreenLeafGaming.Gta.ClientScripts.LoginManager+<>c__DisplayClass1_0.<UserJoined>b__0 () [0x0007e] in <3f4806611d8d4422b8bc5a9a2f7ff9f6>:0
  at System.Threading.Tasks.Task.InnerInvoke () [0x0000f] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0
  at System.Threading.Tasks.Task.Execute () [0x00010] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0
   --- End of inner exception stack trace ---
---> (Inner Exception #0) System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string.
  at System.Data.SqlClient.SqlConnection.ErrorHandler (System.Object sender, Mono.Data.Tds.Protocol.TdsInternalErrorMessageEventArgs e) [0x00057] in <c660f5a12cac430aa34fafb29a22e588>:0
  at Mono.Data.Tds.Protocol.Tds.OnTdsErrorMessage (Mono.Data.Tds.Protocol.TdsInternalErrorMessageEventArgs e) [0x0000b] in <b47bf4b6efd8474e81b79858fb929410>:0
  at Mono.Data.Tds.Protocol.Tds.ProcessMessage (Mono.Data.Tds.Protocol.TdsPacketSubType subType) [0x00102] in <b47bf4b6efd8474e81b79858fb929410>:0
  at Mono.Data.Tds.Protocol.Tds.ProcessSubPacket () [0x0012a] in <b47bf4b6efd8474e81b79858fb929410>:0
  at Mono.Data.Tds.Protocol.Tds.NextResult () [0x0004a] in <b47bf4b6efd8474e81b79858fb929410>:0
  at System.Data.SqlClient.SqlDataReader.NextResult () [0x00042] in <c660f5a12cac430aa34fafb29a22e588>:0
  at System.Data.SqlClient.SqlDataReader..ctor (System.Data.SqlClient.SqlCommand command) [0x00019] in <c660f5a12cac430aa34fafb29a22e588>:0
  at (wrapper remoting-invoke-with-check) System.Data.SqlClient.SqlDataReader:.ctor (System.Data.SqlClient.SqlCommand)
  at System.Data.SqlClient.SqlCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x00068] in <c660f5a12cac430aa34fafb29a22e588>:0
  at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader (System.Data.CommandBehavior behavior) [0x00000] in <c660f5a12cac430aa34fafb29a22e588>:0
  at System.Data.Common.DbCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x00000] in <c660f5a12cac430aa34fafb29a22e588>:0
  at (wrapper remoting-invoke-with-check) System.Data.Common.DbCommand:ExecuteReader (System.Data.CommandBehavior)
  at EFCache.CachingCommand.ExecuteDbDataReader (System.Data.CommandBehavior behavior) [0x00008] in <1802f9202cc445d08c2ad065e84fb563>:0
  at System.Data.Common.DbCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x00000] in <c660f5a12cac430aa34fafb29a22e588>:0
  at (wrapper remoting-invoke-with-check) System.Data.Common.DbCommand:ExecuteReader (System.Data.CommandBehavior)
  at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c (System.Data.Common.DbCommand t, System.Data.Entity.Infrastructure.Interception.DbCommandInterceptionContext`1[TResult] c) [0x00007] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1[TInterceptor].Dispatch[TTarget,TInterceptionContext,TResult] (TTarget target, System.Func`3[T1,T2,TResult] operation, TInterceptionContext interceptionContext, System.Action`3[T1,T2,T3] executing, System.Action`3[T1,T2,T3] executed) [0x0000f] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader (System.Data.Common.DbCommand command, System.Data.Entity.Infrastructure.Interception.DbCommandInterceptionContext interceptionContext) [0x00042] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader (System.Data.CommandBehavior behavior) [0x00046] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Common.DbCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x00000] in <c660f5a12cac430aa34fafb29a22e588>:0
  at (wrapper remoting-invoke-with-check) System.Data.Common.DbCommand:ExecuteReader (System.Data.CommandBehavior)
  at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute (System.Collections.Generic.Dictionary`2[TKey,TValue] identifierValues, System.Collections.Generic.List`1[T] generatedValues) [0x0007e] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update () [0x0002b] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
   --- End of inner exception stack trace ---
  at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update () [0x00080] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.<Update>b__2 (System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator ut) [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T] (T noChangesResult, System.Func`2[T,TResult] updateFunction) [0x00063] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update () [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesToStore>b__35 () [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T] (System.Func`1[TResult] func, System.Data.Entity.Infrastructure.IDbExecutionStrategy executionStrategy, System.Boolean startLocalTransaction, System.Boolean releaseConnectionOnSuccess) [0x0008a] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore (System.Data.Entity.Core.Objects.SaveOptions options, System.Data.Entity.Infrastructure.IDbExecutionStrategy executionStrategy, System.Boolean startLocalTransaction) [0x0002e] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.Objects.ObjectContext+<>c__DisplayClass2a.<SaveChangesInternal>b__27 () [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult] (System.Func`1[TResult] operation) [0x00036] in <5c51bb66ce2c412d8224b39ad2626fbe>:0
  at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal (System.Data.Entity.Core.Objects.SaveOptions options, System.Boolean executeInExistingTransaction) [0x00072] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges (System.Data.Entity.Core.Objects.SaveOptions options) [0x00000] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Internal.InternalContext.SaveChanges () [0x0004d] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
   --- End of inner exception stack trace ---
  at System.Data.Entity.Internal.InternalContext.SaveChanges () [0x0005f] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.Internal.LazyInternalContext.SaveChanges () [0x00008] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at System.Data.Entity.DbContext.SaveChanges () [0x00006] in <b7b192e5a05f4f14a14aa0d5b14aeeeb>:0
  at GreenLeafGaming.Gta.Services.Data.Context.DatabaseContext.SaveChanges () [0x00062] in <9c7aa4bdb7404628b0859d010ed00cab>:0
  at GreenLeafGaming.Gta.ClientScripts.LoginManager+<>c__DisplayClass1_0.<UserJoined>b__0 () [0x0007e] in <3f4806611d8d4422b8bc5a9a2f7ff9f6>:0
  at System.Threading.Tasks.Task.InnerInvoke () [0x0000f] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0
  at System.Threading.Tasks.Task.Execute () [0x00010] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 <---

Have you run into this at all? I’ve double checked and verified I am definitely on the latest version of the repository that you posted. Will continue trying to figure out the problem on my own but, if you have any insight it would be appreciated!

[Josh_Smith]

I was able to figure out what the problem was. The way one of the dependent libraries is formatting date time is like this: 2018-01-28T01:06:48.5071109 MSSQL expects the DateTime to look like this: 01/28/2018 01:06:48 AM. I had to run SQL Profiler and watch the queries executing as soon as I saw the query I realized the format was the issue. I was able to work around this issue by adding the following:

  [NotMapped]
        public DateTime? BirthDate
        {
            get
            {
                if (string.IsNullOrWhiteSpace(BirthDateTimestamp))
                    return null;

                return DateTime.Parse(BirthDateTimestamp);
            }

            set
            {
                if (value == null)
                    BirthDateTimestamp = null;

                BirthDateTimestamp = value.ToString();
            }
        }

        [StringLength(27)]
        public string BirthDateTimestamp { get; private set; }

Not ideal but, it will work for now… Haven’t figured out a way to get the underlying library to submit it in the correct format so that I can properly utilize SQL datetime fields. This issue only exists on Insert or Update.

[Zoull]

Sorry I’m just now getting back to you. I’ve been really busy lately.

I see that you have gotten past the connection string issue on my repository that I haven’t had time to resolve yet.

You could use DateTime.Parse() and specify the provider and/or the style to try and get it to parse your string properly directly or make sure that the application is set in the proper culture to be able to parse your string properly. It might also not be parsing the string correctly because it needs the offset on the very end, something like:

2018-01-28T01:06:48.5071109Z // For UTC
2018-01-28T01:06:48.5071109-7:00 // The end being the offset from UTC that you want to target

Or you could break things down and do something like this:

var birthDate = new DateTime();

birthDate.Year = 2018;
birthDate.Month = 1;
birthDate.Day = 28;
birthDate.Hour = 1;
birthDate.Minute = 6;
birthDate.Second = 48;

Also, maybe take a look at this and see if you can’t find something on there that would help you:

DateTime.Parse Method (System)

Hopefully some of this helps you actually solve your problem. Let me know how it goes and I’ll try and keep up better with this thread to help you.

[Josh_Smith]

I’ve worked around the issue for the most part for now. Here is an example connection string that works for me:

public const string DatabaseContext = "Data Source=localhost;Initial Catalog=dbname;User ID=user;Password=pass;MultipleActiveResultSets=True;Application Name=EntityFramework";

In the sample code provided on the repo you are not actually passing the AppConstants.DatabaseContext to the DatabaseContext itself. If you use this snippet below, it will pass the connection string everytime you instantiate the DatabaseContext.

 public DatabaseContext() : base(AppConstants.DatabaseContext)
 {
      Configuration.LazyLoadingEnabled = true;
 }

As far as the issue with the DateTime, it appears to be an issue in a dependent library most likely the Oracle library or the I18N library. One of those libraries is trying to consistently format the date time into 2018-01-28T01:06:48.5071109 on the Insert & Update statements. The problem is that MSSQL does not like that format and will throw an error consistently. Parsing that kind of DateTime in .NET isn’t an issue the problem is we don’t have control on the way that the SQL Insert & Update statements are formatted or at least I haven’t found a way to control it yet.

[Zoull]

You can add the connection string to the connectionStrings section of the machine.config and then you don’t have to pass the DatabaseContext at all. So then you don’t have to do that work around.

As for the DateTime issue, I haven’t had any problems storing DateTime’s just like I would in any of my other projects, but I’ll take a look into it and see what I can figure out. The I18N library is required by the server for internationalization so that it doesn’t throw an exception when trying to use EntityFramework, so I’m not sure if I can do anything there, but I’ll take a look and see what I can figure out.

Thanks for pointing this stuff out.

[Josh_Smith]

You are using MSSQL right instead of MySql?

[Zoull]

Yes. Using Code First with a Unit of Work pattern for use in C#, so there’s no need to write any SQL statements.

[Josh_Smith]

That’s interesting that you didn’t have anything special to do with DateTime. What version of MSSQL are you using if you don’t mind me asking?

[Zoull]

I use MSSQL 2014 for the actual server instance.