Entity Framework Core 3.0 Preview 4

Diego Vega, the Program Manager of the Entity Framework team recently announced on his blog some of the breaking changes in ASP.NET Core 3.0 Preview 4.

Some of these changes include the following:

  • LINQ queries are no longer evaluated on the client
  • EF Core runtime no longer part of the ASP.NET Core shared framework. In other words, they have to be obtained via NuGet package
  • DotNet EF Tool is no longer part of the .NET Core SDK. It has to be downloaded separately
  • Automatic key generation (reset) after in-memory database is deleted
  • Database connection no longer remains open until a TransactionScope is disposed
  • The concept of query types has been renamed to entities without keys
  • New preview of the Cosmos DB provider for EF Core.  Some of the changes include the usage of deterministic appraoch to key generation and allowing developer to specify Azure region to us. Fantastic!!!
  • EF 6.3 to target .NET Standard 2.1, in addition to already supported .NET Framework versions.

 

Posted in ORM | Leave a comment

Cannot connect to runtime process, timeout after 10000 ms

If you are a web developer, it’s given that you also write JavaScript code.

I am building an ASP.NET Core app in VS 2017 and I needed to install a feature that I chose to write in JavaScript. While everything worked fine initially, however, when I stopped the app and re-ran it, I got a nasty error. See image below:

I decided to shut down Visual Studio, launched it and open my project, but still in vain.

Turned out that in Tools => Option => Debugging => General, there is an item (circled in red) i.e. Enable JavaScript debugging for ASP.NET (Chrome, Edge and IE).

After unchecking this item and running the app, everything appeared just fine.

I certainly don’t recommend this to be a solution, but rather a workaround. Meanwhile, you can use developer tools in your favorite browser and continue debugging. Alternatively, you can also launch Visual Studio 2017 and select “Run as administrator.”

Posted in Uncategorized | Leave a comment

Query Types in EF Core

If you have been using Entity Framework 6.x or EF Core, then you are already familiar with Entities and its usage.

Query Types is a new addition to EF Core 2.1 which you can refer to Entities as its cousin.

What are some of the Use Cases

  • Is used when working with entities with no key propertie(s) defined, may it be a Table, Stored Procedure or a View
  • Is used when mapping to queries specified in the model
  • Is used for ad hoc queries where changes are not tracked in DbContext. In other words, queries that are strictly read-only

So let’s get Started

First let’s define the two POCO classes. Each of these classes below maps to the tables in the database.

public class BaseAggr
    {
        public long BaseAggrId { get; set; }
        [Display(Name = "Portfolio ID")]
        public string PortfolioId { get; set; }

        [Display(Name = "Scenario ID")]
        public long? ScenarioId { get; set; }

        [Display(Name = "Sum Pnl")]
        public double? SumPnl { get; set; }

        [Display(Name = "Business Date")]
        [DataType(DataType.Date)]
        public DateTime? BusinessDate { get; set; }
    }
    public class WhatIfAggr
    {
        public long WhatifAggrId { get; set; }

        [Display(Name="Portfolio ID")]
        public string PortfolioId { get; set; }

        [Display(Name ="Scenario ID")]
        public long? ScenarioId { get; set; }

        [Display(Name ="Sum PnL")]
        public double? SumPnl { get; set; }

        [Display(Name ="Business Date")]
        [DataType(DataType.Date)]
        public DateTime? BusinessDate { get; set; }
    }

Next, let’s create a database view. As you can tell from the query below that it’s using Common Table Expression to form a result set.

CREATE OR ALTER VIEW [dbo].[vwVarByPortfolios] AS
SELECT * FROM 
(SELECT portfolio_id, scenario_id as scenario_id, sum_pnl as var_pnl 
	FROM (SELECT portfolio_id,scenario_id, sum_pnl, RANK() OVER (PARTITION BY portfolio_id ORDER BY sum_pnl DESC) AS pnl_rank FROM base_aggr) AS Agg
	WHERE pnl_rank = 98
) AS A	INNER JOIN
	
(SELECT portfolio_id AS wi_portfolio_id, scenario_id AS wi_scenario_id, sum_pnl AS wi_var_pnl 
	FROM (SELECT portfolio_id,scenario_id, sum_pnl, RANK() OVER (PARTITION BY portfolio_id ORDER BY sum_pnl desc) AS pnl_rank from whatif_aggr) AS Agg
	WHERE pnl_rank = 98
 ) AS X
 ON portfolio_id = wi_portfolio_id;
GO

In addition to the classes above, you would also need a class for Query Type to hold results from the database view like so:

public class PortfolioView
{
public string PortfolioID { get; set; }
public long ScenarioID { get; set; }
public double VarPnl { get; set; }
public string WiPortfolioID { get; set; }
public long WiScenarioID { get; set; }
public double WiVarPnl { get; set; }
}

Next, we will create a DbQuery property in the DbContext class in order for it to be recognized as a query type.

public DbQuery PortfolioView { get; private set; }

Next, we will write a fluent API in DbConext’s OnModelCreating method to map the view to the Query Type i.e. vwVarByPortfolios. The “ToView” extension method is used to configure the mapping especially when a relational database is being targeted.

modelBuilder.Query().ToView("vwVarByPortfolios")
                .Property(p => p.PortfolioID).HasColumnName("portfolio_id");

            modelBuilder.Query().ToView("vwVarByPortfolios")
                .Property(p => p.ScenarioID).HasColumnName("scenario_id");

            modelBuilder.Query().ToView("vwVarByPortfolios")
                .Property(p => p.VarPnl).HasColumnName("var_pnl");

            modelBuilder.Query().ToView("vwVarByPortfolios")
                .Property(p => p.WiPortfolioID).HasColumnName("wi_portfolio_id");

            modelBuilder.Query().ToView("vwVarByPortfolios")
                .Property(p => p.WiScenarioID).HasColumnName("wi_scenario_id");

            modelBuilder.Query().ToView("vwVarByPortfolios")
                .Property(p => p.WiVarPnl).HasColumnName("wi_var_pnl");

Please note, DbQuery does not require a primary key to be defined unlike its DbSet<T> counterpart. The Id’s are being used here just because they are needed.

What you cannot Do:

Couple things worth noting is that in this version i.e. EF Core 2.2, EF Core does not allow Views to be created in the database when migrations are run. Conversely, you cannot reverse engineer views yet. This will most likely be available in the future version of EF Core i.e. 3.0. Here’s the EF Core Roadmap in case you wish you know further.

Lastly, you can query the database view in your console app or a Web App, although in this case, the latter is being used along with Razor Pages.

 public class PortfolioModel : PageModel
    {
        private readonly DataAggr.Models.DataAggrContext _context;

        public PortfolioModel(DataAggrContext context)
        {
            _context = context;
        }

        [BindProperty]
        public IList PortfolioView { get; set; }

        public async Task OnGetAsync()
        {
            PortfolioView = await _context.Query()
                .ToListAsync();
        }
    }

A quick snapshot of the output of the View from inside SQL Management Studio.

Output on a Razor Page in ASP.NET Core.

Hope you found the article useful!

Posted in .NET, ORM | Leave a comment

Data Seeding in EF Core

Data seeding is a process where you populate the initial set of data into the database. I’ll explain some of the rudimentary steps needed for creating seed data in your app.

Prior to EF Core 2.1, seeding data was not quite straight-forward. However with this version, you can seed data using the following steps:

  1. Download the necessary packages from NuGet
  2. Create your Context Class
  3. Write Seed Data
  4. Run Migrations
  5. Apply data changes to your database

Let’s begin…

Download Packages: You need the following packages after you create a new .NET Core console app:

  • Microsoft.EntityFrameworkCore
  • Microsoft.EntityFrameworkCore.SqlServer
  • Microsoft.EntityFrameworkCore.Tools

Context Class: Create a new context class which would derive from DbContext. DbContext represents a session with the database and is used to perform CRUD operations against your database. It’s also a combination of Unit of Work and Repository patterns.


public class DataSeedContext : DbContext
    {
        public DbSet Blog { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlServer(
                @"server=obi-oberoi; database=demo.DotNetConf; trusted_connection=true;");
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity()
                .Property(u => u.Url)
                .IsRequired();

            modelBuilder.Entity().HasData(
                new Blog { BlogId = 1, Url = "http://ObiOberoi.com" },
                new Blog { BlogId = 2, Url = "http://microsoft.com" },
                new Blog { BlogId = 3, Url = "http://cnn.com" },
                new Blog {  BlogId = 4, Url = "https://yahoo.com"},
                new Blog { BlogId = 5, Url = "https://tesla.com" },
                new Blog { BlogId = 6, Url = "https://meetup.com/mississauganetug"},
                new Blog { BlogId = 7, Url="https://td.com"},
                new Blog { BlogId = 8, Url="https://meetup.com/TorontoNETUG"}
                );
        }
    }

Create a Blog class like so:


public class Blog
    {
        public int BlogId { get; set; }
        [Required]
        public string Url { get; set; }
    }

Be sure to specify the type of database you wish to use. In the example, SQL Server is being used. This is defined in the Context class’s OnConfiguring method.


protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlServer(
                @"server=obi-oberoi; database=demo.DotNetConf; trusted_connection=true;");
        }

Run Migration: Just a quick overview of Migrations. Migrations allow you to keep your database in sync with your code. EF Core uses your mapping configurations to produce a snapshot of what your database should look like. Your model may represent tables, its primary keys, foreign keys, indexes, constraints and such.

Whenever you make a change to your POCO class i.e. add or remove a property, you ought to run a PowerShell command like so:

Add-Migration <migration name>

The Add-Migration command computes a database schema from your entity classes and scaffolds a migration with Up and Down methods which consists of the current version of the database to the new version it computes.

Apply Changes to Database: You do this by simply issuing the following command in PowerShell and you are set:

Update-Database

So, this was a quick intro to seeding data into the database.

Posted in .NET, ORM | Leave a comment

Import CSV into SQL Server using VBA

You have heard the famous cliché, “there are many ways to skin a cat”.

That said, there are many ways to import a flat file into SQL Server. Some of them include the following:

  • SSMS – Select Database -> Right Click Tasks -> Import Flat File
  • SQL Server Integration Services (SSIS)
  • Programmatically – Using your favourite language of choice (C# or VB.Net)
  • PowerShell
  • VBA

In this post I’m going to show the Power Users how you can leverage your VBA skills to write a simple Macro and then use it to import a flat file into SQL Server.

What You’ll Need

  1.  SQL Server Management Studio (download here for free)
  2.  Microsoft Excel (2010 or greater)

What are we working With

  • CSV file
  •  Database table

CSV File (this file contains about 15,000 records. For brevity, we are showing a dozen records).

drilldown_request_id dim_1 scenario_id dim_1_pnl
102 PROD_013 96 39.43999672
102 PROD_001 23 -63.47999954
102 PROD_011 4 -5.650003552
102 PROD_015 73 -3.210000992
102 PROD_014 21 19.40999985
102 PROD_014 71 -2.779999018
102 PROD_005 27 -123.8400006
102 PROD_010 2 -56.66000366
102 PROD_012 16 -37.25999832
102 PROD_008 58 -112.4700006
102 PROD_007 54 76.349998
102 PROD_007 44 88.24000001

Database Table Schema:

What Won’t Work

If you simply copy and paste records from the .csv file in SQL Management Studio and attempt to insert them, it would fail. That’s because, you need to stage the data by massaging it such that it is syntactically correct. This means wrapping single quotes i.e. (‘PROD_001 ‘) around every value in dim_1 column and separating each of the columns by comma (,). Now, with over 15000 records, this is next to impossible.

Use Macro in VBA

Open the .csv file in Excel like so.

Hit Alt-F11 to open VBA code surface. Here, simply write a Sub() method

Sub ImportData()
    On Error Resume Next
    
    Dim drillDownRequestId As Integer
    Dim dim1 As String
    Dim scenarioId As Integer
    Dim dim1Pnl As Double
    Dim sTemp As String
    Dim SQLScript As String
    Dim i As Integer
    
    For i = 2 To 15001
        drillDownRequestId = ThisWorkbook.Worksheets("drilldown_table").Range("A" & i).Value
        dim1 = ThisWorkbook.Worksheets("drilldown_table").Range("B" & i).Value
        scenarioId = ThisWorkbook.Worksheets("drilldown_table").Range("C" & i).Value
        dim1Pnl = ThisWorkbook.Worksheets("drilldown_table").Range("D" & i).Value
                
        SQLScript = "insert into drilldown values(" & drillDownRequestId & ",'" & dim1 & "'," & scenarioId & "," & dim1Pnl & ")"
        
        ThisWorkbook.Worksheets("drilldown_table").Range("F" & i).Value = SQLScript
    Next i
End Sub

Now, you can Hit F5 and have the program run all the way. It’ll probably take a second or so to process 15000 records.

Once the program has run successfully, examine the SQL Script in column F of your Excel spreadsheet. As a quick test, copy the script from a single cell and paste it into SSMS and run it. If the operation executes successfully, you know that the script is syntactically correct.

Alternatively, you can set a breakpoint in the For loop by pressing F9 and then hit F8 to step into the code.

Excel

SQL

Please remember there are different horses for different courses . Sometimes one situation doesn’t fit all. This blog post is intended for VBA power users/developers. Those of you that write code in other languages, but have not used VBA, this might be a good primer.

Hope you found this simple post useful 🙂

Obi

Posted in SQL Server | Leave a comment

How to create an .EXE using .NET Core Console App

In the previous exercise, we learnt how to publish and execute a .NET Core Console App! In case you want to review that exercise, you can access it from here.

In this exercise, we’ll look at a quick way to turn a .DLL to an .EXE. Please note, when you build a .NET Core console app, it compiles the app to a DLL.

Here’s I’ll show you how to create an EXE:

  • Go to the command prompt and navigate to the location where the project is stored
  • Issue the following command: dotnet build -r win10-x64

You’ll find the .EXE in the folder i.e. Win10-x64. See image below:

Hope this was helpful.

Posted in .NET | Comments Off on How to create an .EXE using .NET Core Console App

How to Publish and Execute a .NET Core Console App!

Publishing a .NET Core app is as easy as 1,2,3. Here’s how:

After you have created the console app, make sure you select Release from the Solution Configurations drop-down menu.

  Now right-click on the project node inside Solution Explorer and choose Publish from the menu item. See image below:

Hit Publish, and you are set!

In order to run the app from command prompt, the quick and easy way to navigate to the location where the .dll is stored, you can do the following:

  • Open Windows Explorer
  • Navigate to the DLL file. It’ll be your project Name with a .DLL extension

  • Point your cursor to the start of your drive (see image below) and type cmd followed by space and hit Enter. This neat shortcut will take you straight to the command prompt.

  • In order to run your program, you need to type: dotnet xxx.dll where xxx is your file name

That’s it!

Happy Consoling 🙂

Posted in .NET | Tagged , | Comments Off on How to Publish and Execute a .NET Core Console App!

How to use .NET Core Console app?

Console apps have been around since .NET’s inception i.e. 2002. Over the years, it has had a bit of a windows dressing with additional APIs, integration with .NET Core and the like.

Let’s look at a simple console app written in .NET Core that simply sends an email using the Net.Mail API.

using System;
using System.Net.Mail;

namespace SMTP
{
    class Program
    {
        static void Main(string[] args)
        {
            SendTestEmail();
            Console.WriteLine("The email was sent successfully!");
            Console.ReadLine();
        }

        private static void SendTestEmail()
        {
            MailMessage mail = new MailMessage("Obi@eOberoi.com", "ooberoi@hotmail.com");
            SmtpClient client = new SmtpClient
            {
                Port = 587,
                DeliveryMethod = SmtpDeliveryMethod.Network,
                UseDefaultCredentials = true,
                Host = "smtp.google.com",
                Credentials = new System.Net.NetworkCredential("Obi@gmail.com", "MyPassword")
            };
            
            mail.Subject = "Testing Console App!";
            mail.Body = "How is it going Obi!";
            client.Send(mail);
        }
        
    }

Links to similar blog posts (coming soon!):

Posted in .NET | Comments Off on How to use .NET Core Console app?

How to combine multiple stored procedures into a single resulset

Let’s say, you have multiple stored procedures each of which is tied to a partial view or View Component in MVC. Now, while this may seem like a logical thing to do, but what if you want the entire payload to return to a single View.

Well, how about combine all the stored procedures and return a single result set. Here’s how in three simple steps.

First, let’s take a look at the diagram below:

The Employee table has the following records:

Following are the three stored procedures that I have created for the purposes of this demo:

CREATE OR ALTER PROCEDURE [dbo].[spGetMinimumSalary]	
	@MinSalaryRange MONEY,
	@MaxSalaryRange MONEY
AS
BEGIN
	SET NOCOUNT ON;
	 
	SELECT
		FirstName, 
		LastName, 
		Gender
	FROM 
		Employee 
	WHERE
		(Salary BETWEEN @MinSalaryRange AND @MaxSalaryRange)
END
CREATE OR ALTER PROCEDURE [dbo].[spGetMedianSalary]	
	@MinSalaryRange MONEY,
	@MaxSalaryRange MONEY
AS
BEGIN	
	SET NOCOUNT ON;
  
	SELECT
		FirstName, 
		LastName, 
		Gender
	FROM 
		Employee 
	WHERE
((Salary > @MinSalaryRange) AND (Salary <= @MaxSalaryRange))
END
CREATE OR ALTER PROCEDURE [dbo].[spGetMaximumSalary]	
	@Salary MONEY	
AS
BEGIN
	SET NOCOUNT ON;
	   
	SELECT
		FirstName, 
		LastName, 
		Gender
	FROM 
		Employee 
	WHERE
		(Salary > @Salary)
END

Now, comes the interesting part. Let's create a temporary table:

CREATE TABLE #TmpSalary 
(
	FirstName NCHAR(25),
	LastName NCHAR(25),
	Gender NCHAR(10)
)

As you can see, the temporary table only has the structure, but no data:

Select * from #TmpSalary

Now, let's insert the records into the temporary table that are returned from executing the stored procedures like so:

INSERT INTO #TmpSalary
EXEC spGetMinimumSalary 0, 50000

INSERT INTO #TmpSalary
EXEC spGetMedianSalary 50000, 75000

INSERT INTO #TmpSalary
EXEC spGetMaximumSalary 75000

Let's view the records now:

Select * from #TmpSalary

There you have it! 🙂

Posted in SQL Server | Comments Off on How to combine multiple stored procedures into a single resulset

How to pass delimited list to a Stored Procedure

Let’s write a Table-valued function below. This can be created by navigating to Functions – Table-Valued Functions inside SQL Management Studio.

Just an FYI, I am using the AdventureWorks database, but this  sample can be used in any situation.

CREATE FUNCTION [dbo].[fn_SplitStateProvinceCode]
(
   @List VARCHAR(MAX),
   @Delimiter CHAR(1)
)
RETURNS TABLE 
AS 
  RETURN ( SELECT Item = CONVERT(varchar(500), Item) FROM
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
        FROM ( SELECT [XML] = CONVERT(XML, ''
        + REPLACE(@List, @Delimiter, '') + '').query('.')
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
      WHERE Item IS NOT NULL
  );

For testing, you can declare a variable and test the statement by passing the @List and delimiter like so. As you can see, in the sample below, the delimiter is ‘,’.

Caution: The delimited string is strictly delimited by a comma and has no space in between province codes.

DECLARE @List VARCHAR(MAX)
SET @List = 'AB,ON,TX,VIC,WA'
SELECT CODES = ITEM FROM dbo.[fn_SplitStateProvinceCode (@List, ',') 

If you wish to write it in a query using the Where clause and using ‘IN’ you can do the following:

DECLARE @List VARCHAR(MAX)
SET @List = 'AB,ON,TX,VIC,WA'
Select * from Person.StateProvince 
WHERE StateProvinceCode IN (Select CODES = ITEM FROM dbo.fn_SplitStateProvinceCode (@List, ','))	

Cheers,

Obi

Posted in SQL Server | Comments Off on How to pass delimited list to a Stored Procedure