2016年10月29日 星期六

[Entity Framework Core] Migrations with EF Command (EF Core 1.X)

 ASP.NET Core    Entity Framework Core   .NET Core CLI   CLI command


Introduction


So in .NET Core projects, we can use EF Command to execute database migrations.
However, in current DOTNET Core 1.0.0-preview, the EF Command only works on the runnable projects (such like Website, Console…)*.

See issue :


And that means we could only put our DbContext and DAOs in the website project?
That seems it will mess up the website with too much responsibility within it.

In this article, we will try to make a clean architecture for Entity framework Core and let the EF Command works.




Supported framework
EF supports .NET Core CLI commands on these frameworks:
.NET Framework 4.5.1 and newer. (“net451”, “net452”, “net46”, etc.)
.NET Core App 1.0. (“netcoreapp1.0”)


Environment

Visual Studio 2015 Update 3
DOTNET Core 1.0.0- DOTNET Core 1.0.0-preview


Implement


Create two projects
1.  Sample.Website : ASP.NET Core
2.  Sample.DAL : Class library (.NET Core)

Sample.DAL will have the DbContext, Database Models.


Data Access layer (DAL)

Install the following package(s):



/Models/DAO/Customer.cs

You can create your own DAO. This one is just for reference.

public class Customer
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        [StringLength(100)]
        [Required]
        public string Name { get; set; }
        [StringLength(100)]
        public string Phone { get; set; }
        public int Age { get; set; }
        [StringLength(200)]
        public string  Description { get; set; }
    }




/DbContext/NgDbContext.cs

public class NgDbContext : DbContext
{
        public NgDbContext(DbContextOptions options) : base(options)
        {

        }
        public DbSet<Customer> Customers { get; set; }
}




Presentation Layer (Website)


Install the packages.
3.  Microsoft.EntityFrameworkCore.Tools: "1.0.0-preview2-final"

(For Sql server)


The most important is Microsoft.EntityFrameworkCore.Tools, which supports the EF Command Line tooling.



Test if the EF command works

Open command line or Powershell or Package Management Console*, change directory to the root of the website, and type the command to see if EF Command works.

$> dotnet ef --version

Or $> dotnet ef --help to see the helps.



Sometimes you may see the following error message:
dotnet ef throws Could not load file or assembly Microsoft.DotNet.Cli.Utils

Then go clear(delete) the nuget caches
1.  C:\Users\{LoginName}\.nuget\packages\.tools\Microsoft.EntityFrameworkCore.Tools
2.  C:\Users\{LoginName}\.nuget\packages\Microsoft.EntityFrameworkCore.Tools

and restore them under the website’s root.
$> dotnet restore

Also see this reference.




Start Migration

The original add-migration and update-database commands are simple, like this

$> dotnet ef migrations add [migration_name] –c [DbContext_name]

$> dotnet ef database update [migration_name]
PS. [migration_name] = “0” will revert all migrations, or skip this argument to apply all pending migrations.


However, while we put the DbContext in the other project but not the one we are running EF Command, so we have to appoint the target project with DbContext and the startup project for EF Command.


Add migration

dotnet ef  --project ../Sample.DAL --startup-project . migrations add [migration_name] -c [DbContext_name]

Update database

dotnet ef  --project ../Sample.DAL --startup-project . database update



Implement IDbContextFactory<DbContext>

Okay, now it seems works but right away we get the following error:

No parameterless constructor was found on 'NgDbContext'. Either add a parameterless constructor to 'NgDbContext' or add an implementation of 'IDbContextFactory<NgDbContext>' in the same assembly as 'NgDbContext'.


This error is telling us that the migration would need to create a DbContext instance to get things done.  We will create a class and implement IDbContextFactory<TContext> in Sample.DAL.


MigrationFactory.cs

public class MigrationFactory : IDbContextFactory<NgDbContext>
{
        public NgDbContext Create()
        {
            var builder = new DbContextOptionsBuilder<NgDbContext>();
            builder.UseSqlServer(Configuration.DEFAULT_CONNECT_STR);
            return new NgDbContext(builder.Options);
        }
}

PS. I create another class: Configuration, to store the database connection string.


Re-run the add-migration and update-database commands, the CLI create/update the table: Customers for us.

For more EF Command usage, take a look at docs.efproject.net




How to initialize data

The data initializing function is on the roadmap of Entity framework Core team and will be released in the future.

So far we can make a trick to achieve the data-initializing.

Create a class: Configuration,

Configuration.cs

public class Configuration
    {
        //Connection string for code first
        public static string DEFAULT_CONNECT_STR = "Server=.;Database=XXX;Trusted_Connection=True;MultipleActiveResultSets=true";

        public void Seed()
        {
            var dbContext = DbContextFactory.Create(DEFAULT_CONNECT_STR);

#if (Seed)
            this.initCustomers(dbContext);
#endif
        }

        private void initCustomers(NgDbContext dbContext)
        {
            dbContext.Customers.Add(new Customer { Name = "JB Lin",  Phone = "0933XXXXXX", Age = 35, Description = "JB is a good programmer :)" });
            dbContext.Customers.Add(new Customer { Name = "Leia Lin",Phone = "-", Age = 3, Description = "A cute girl!" });
            dbContext.SaveChanges();
        }
    }


We should call the Seed function after running add-migration command but before update-database command. Add the Seed function to the migration class which is created by the CLI.



So when the update-database command is executed, the data is also be initialized.





Reference



2016年10月27日 星期四

[C#] Func vs Expression Func


今天在找一個Performance issue時, 打開Sql profiler突然看到一段EF查詢直接做Full table scan 追了一下原來是傳過去的條件型態宣告為: Func<T,bool>  注意FuncLinqlambda expression中並無法直接轉為sql,必須改為傳遞Expression<Func<T>>


If you want your lambda to be executed in SQL, you need to pass it as an Expression, not a Function.

Func<T> denotes a delegate which is pretty much a pointer to a method and Expression<Func<T>> denotes a tree data structure for a lambda expression.


2016年10月26日 星期三

[ASP.NET Core X Angular](5) - NLog and JSNLog

 Angular    MVC6    ASP.NET Core    NLog    JSNLog 




Introduction


Before we go farther, I want to talk about the logging and use the logging framework for both the frontend and backend.

We will us NLog and JSNLog to fulfill the purpose.




Environment

Visual Studio 2015 Update 3
NPM: 3.10.3                                    
Microsoft.AspNetCore.Mvc 1.0.0
angular 2: 2.1.0
NLog.Extensions.Logging 1.0.0-rtm-alpha4
System.Data.SqlClient 4.1.0
JSNLog.AspNetCore 2.20.0
Newtonsoft.Json 9.0.1


Packages/JS/type definition


We will install the following packages thru Nuget
4.  System.Data.SqlClient (Optional, install it when you wanna keep the log in sql server)




And install the JSNLog js files by NPM.

$> npm intall jsnlog --save



Include jsnlog js

Layout.cshtml

<head>
    <!-- ... -->
    <script src="~/lib-npm/jsnlog/jsnlog.min.js"></script>
</head>



Update project.json to support .NET framework 4.5.2

The ASP.NET core template default supports only .NET Core 1.0 (netcoreapp1.0).
But this will cause our website to occur an error like this –

InvalidOperationException: Can not find compilation library location for package 'JSNLog'






The error may happen when the package was compiled in lower .NET frameworks.
Depends on the official document of JSNLog, we will need to make our website to support .NET framework 4.5.2 as well.


project.json

{
  "dependencies": {
    //"Microsoft.NETCore.App": {
    //  "version": "1.0.0",
    //  "type": "platform"
    //},
    "Microsoft.AspNetCore.Diagnostics": "1.0.0",
    "Microsoft.AspNetCore.Server.IISIntegration": "1.0.0",
    "Microsoft.AspNetCore.Server.Kestrel": "1.0.0",
    "Microsoft.Extensions.Logging": "1.0.0",
    "Microsoft.Extensions.Logging.Console": "1.0.0",
    "Microsoft.Extensions.Logging.Debug": "1.0.0",
    "Microsoft.Extensions.Logging.Filter": "1.0.0",
    "Microsoft.AspNetCore.Mvc": "1.0.0",
    "Microsoft.AspNetCore.StaticFiles": "1.0.0",
    "Microsoft.AspNet.StaticFiles": "1.0.0-rc1-final",
    "es6-shim.TypeScript.DefinitelyTyped": "0.8.1",
    "NLog.Extensions.Logging": "1.0.0-rtm-alpha4",
    "System.Data.SqlClient": "4.1.0",
    "JSNLog.AspNetCore": "2.20.0",
    "Newtonsoft.Json": "9.0.1"
  },

  "tools": {
    "Microsoft.AspNetCore.Server.IISIntegration.Tools": "1.0.0-preview2-final"
  },

  "frameworks": {
    "net452": {
      "dependencies": {
      }
    },
    "netcoreapp1.0": {
      "imports": [
        "dotnet5.6",
        "dnxcore50",
        "portable-net45+win8"
      ],
      "dependencies": {
        "Microsoft.NETCore.App": {
          "version": "1.0.0",
          "type": "platform"
        }
      }
    }
  },
  //....
}





NLog config


We have to update NLog settings in NLog.config.

NLog.config

Copy the following content to your NLog.config, and modify the file pathes, database connection string, targets and rules.

<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://www.nlog-project.org/schemas/NLog.xsd NLog.xsd"
      autoReload="true"
      throwExceptions="true"
      internalLogLevel="Error" internalLogFile="c:\temp\nlog-internal.log" >

  <!—LOG format-->
  <variable name="Layout" value="${longdate} | ${level:uppercase=true} | ${logger} | ${message} ${newline}"/>
  <variable name="LayoutFatal" value="${longdate} | ${level:uppercase=true} | ${logger} | ${message} | ${exception:format=tostring} ${newline}"/>
  <variable name="LayoutEvent" value="${date}: ${message} ${stacktrace}"/>

  <!—LOG path -->
  <variable name="LogTxtLocation" value="${basedir}/App_Data/Logs/${shortdate}/${logger}.log"/>
  <variable name="LogTxtLocationFatal" value="${basedir}/App_Data/Logs/${shortdate}/FatalFile.log"/>
  <variable name="ProjectName" value="Angular.Mvc"/>


  <targets>
    <!—Text file-->
    <target name="File" xsi:type="File" fileName="${LogTxtLocation}" layout="${Layout}" />
    <target name="FileFatal" xsi:type="File" fileName="${LogTxtLocationFatal}" layout="${LayoutFatal}"/>
    <!—Event log-->
    <!--<target name="Event" xsi:type="EventLog" source="${ProjectName}" log="Application" layout="${LayoutEvent}" />-->
    <!—Log Viewer(Optional)-->
    <target name="Sentinel" xsi:type="NLogViewer" address="udp://127.0.0.1:3333"/>
    <!--Database-->
    <target name="LogDatabase" xsi:type="Database" >
      <connectionString>
        Data Source=Your-DB-Server-name;Initial Catalog=Your-DB-Name;Integrated Security=True;
      </connectionString>
      <commandText>
         INSERT INTO AspnetCoreLog (
              Application, Logged, Level, Message,
              Logger, CallSite, Exception
              ) values (
              @Application, @Logged, @Level, @Message,
              @Logger, @Callsite, @Exception
              );
      </commandText>
    
      <parameter name="@application" layout="AspNetCoreNlog" />
          <parameter name="@logged" layout="${date}" />
          <parameter name="@level" layout="${level}" />
          <parameter name="@message" layout="${message}" />
          <parameter name="@logger" layout="${logger}" />
          <parameter name="@callSite" layout="${callsite:filename=true}" />
          <parameter name="@exception" layout="${exception:tostring}" />
    </target>
  </targets>

  <rules>
    <logger name="*" levels="Trace, Debug, Info, Warn"  writeTo="File" />
    <logger name="*" levels="Error, Fatal"                           writeTo="FileFatal,LogDatabase" />
    <logger name="*" levels="Trace, Debug, Info, Warn, Error, Fatal" writeTo="Sentinel" />
  </rules>
</nlog>


Don’t forget to create the logging table in your database.

CREATE TABLE [dbo].[AspnetCoreLog] (
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [Application] [nvarchar](50) NOT NULL,
      [Logged] [datetime] NOT NULL,
      [Level] [nvarchar](50) NOT NULL,
      [Message] [nvarchar](max) NOT NULL,
      [Logger] [nvarchar](250) NULL,
      [Callsite] [nvarchar](max) NULL,
      [Exception] [nvarchar](max) NULL,
    CONSTRAINT [PK_dbo.Log] PRIMARY KEY CLUSTERED ([Id] ASC)
      WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]



Config ASP.NET core


Open Startup.cs and update the Configure function,
1.  Add NLog to the Logger factory
2.  Set the jsnlog adapter to Logger factory

Startup.cs

public class Startup
{
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();
        }

        public void Configure(
            IApplicationBuilder app,
            IHostingEnvironment env,
            ILoggerFactory loggerFactory)
        {
            #region NLog
            //add NLog to ASP.NET Core
            loggerFactory.WithFilter(new FilterLoggerSettings{
                    { "Microsoft", LogLevel.Warning },
                    { "System", LogLevel.None },
                    { "Default", LogLevel.Debug }
            }).AddNLog();

            env.ConfigureNLog("NLog.config");

            #endregion

            #region JSNLog
            // Configure JSNLog
// See jsnlog.com/Documentation/Configuration
            var jsnlogConfiguration = new JsnlogConfiguration();
            app.UseJSNLog(new LoggingAdapter(loggerFactory), jsnlogConfiguration);
            #endregion

            // …
}
    }
}



Copy jslog: js and type definitions

gulpfile.js

Add the following tasks to gulp.

//skip...

//jsnlog
gulp.task('copy-jsnlog', function () {
    return gulp.src(root_path.nmSrc + "/jsnlog/jsnlog*.js", {
        base: root_path.nmSrc + '/jsnlog/'
    }).pipe(gulp.dest(root_path.package_lib + '/jsnlog/'));
});

gulp.task('copy-jsnlog-typing', function () {
    return gulp.src(root_path.nmSrc + "/jsnlog/Definitions/jsnlog.d.ts", {
        base: root_path.nmSrc + '/jsnlog/Definitions/'
    }).pipe(gulp.dest(root_path.package_lib + '/typings/'));
});


gulp.task("copy-all", [
    //...
    "copy-jsnlog",
    "copy-jsnlog-typing"
])

Run the task to copy them to $/wwwroot/lib-npm





Start logging



Backend (MVC controller)

public class BaseController: Controller
    {
       
        protected static Logger _logger = LogManager.GetCurrentClassLogger();

        [Route("[action]")]
        public IActionResult Index()
        {
            _logger.Debug($"Areas:Basic,Controller:Customer,Action:Index");
            _logger.Error($"Areas:Basic,Controller:Customer,Action:Index");
            return View();
        }
}



Frontend

Add the reference path to the jsnlog type definitions in the typescript for intelliSense.

/// <reference path="../../../lib-npm/typings/jsnlog.d.ts" />

And now we can log anything in angular components.

JL("Angular2").debug("Saving a customer!");


Demo




What’s next?

In the next tutorial, we will use http module and class to get and save the data thru http restful service.


Github









Reference