Providing the code details and explanations for major data-related processes and workflows in the RESTful data service sample application evolved from the legacy ASP.NET Web API to different versions of ASP.NET Core technologies. The code issues and resolutions are particularly described to help developers work on their own projects. The source code files for the .NET versions currently supported by the Microsoft (.NET 5, .NET Core 3.1, .NET Core 2.1, and .NET Framework 4x) are available for downloading.
Introduction
The RESTful data service API has been a mainstream data layer application type for many years. With the technology and framework evolutions, the older code and structures would inevitably be replaced with the newer ones. For the work on migrating the sample data service application to the next ASP.NET generations or higher versions, my objectives are to take the advantages of the newer technology and framework but still keep the same functionality and request/response signatures and workflow. Thus, any client application will not be affected by changes in data service migrations from either ASP.NET Web API to the ASP.NET Core or between versions of the ASP.NET Core. This article is not a step-by-step tutorial, for which audiences can reference other resources if needed, but rather share the completed sample application source code together with below topics and issue resolutions:
Setting Up and Running Sample Applications
To run the SM.Store.CoreApi solution with the .NET Core version you prefer, you need the respective Visual Studio 2019 or 2017 and .NET Core versions installed on your machine:
- ASP.NET Core 5.0: Visual Studio 2019 (16.8.x or above is needed; .NET 5.0 SDK included which also back supports the .NET Core 2.1 and 3.1 SDKs.)
- ASP.NET Core 3.1: Visual Studio 2019 (16.4.x or above is needed; .NET Core 3.1 SDK included)
- ASP.NET Core 2.1: Visual Studio 2019 or 2017 15.7 (or above) and .NET Core 2.1 SDK
If the .NET Core version you have installed is version 2.1.302 (or above), you can use the command "dotnet --list-sdks" on the Command Prompt window to see the list of all installed .NET Core SDK library versions. This command is only available after installing version 2.1.302 or above.
I also recommend downloading and installing the free version of the Postman as your service client tool. After opening and building the SM.Store.CoreApi solution with Visual Studio, you can select one of the available browsers from the IIS Express button dropdown on the menu bar, and then click that button to start the application.
No database needs to be set up initially since the application uses the in-memory database with the current configurations. The built-in starting page will show the response data in the JSON format obtained from a service method call, which is just a simple way to start the service application with the IIS Express on the development machine.
You can now keep the Visual Studio session open and call a service method using the Postman. The results, either the data or error, will be displayed in the response section:
The downloadable sources AspNetCore5.0_DataService include the TestCasesForDataServices.txt file that can be used for all types and versions of the sample application projects. The file contains many cases of requesting data items. Feel free to use the cases for your test calls to both newer SM.Store.CoreApi and legacy SM.Store.WebApi applications.
If you would like to use SQL Server database or LocalDB, you can open the appsettings.json file and perform the following steps:
-
Remove the UseInMemoryDatabase line or set its value to false under the section AppConfig.
-
Update the StoreDbConnection value under the ConnectionStrings section with your settings. For example, if you use the SQL Server LocalDB, you can enable the connection string and replace the <your-instance-name> with your LocalDB instance name. You can even change the StoreCF8 to your own database name.
"StoreDbConnection": "Server=(localdb)\\<your-instance-name>;
Database=StoreCF8;Trusted_Connection=True;MultipleActiveResultSets=true;"
-
When starting the Visual Studio solution by pressing F5, the database will automatically be created and the startup.html page will be shown.
If you need to set up and run the legacy ASP.NET Web API sample application, you can do the following after downloading the WebApi_DataServices.
-
Open the SM.Store.WebApi solution with the Visual Studio 2017 or 2019 (also working with version 2015).
-
Rebuild the solution, which automatically downloads all configured libraries from the NuGet.
-
Set up the SQL Server 2017 or 2019 LocalDB, or other SQL Server instance even with older versions on your local machine. Please adjust the connectionString in the web.config file to point to your database instance.
-
Make sure the SM.Store.Api.Web is the startup project and than press F5. This will start the IIS Express and the Web API host site, automatically create the database in your database instance, and populate tables with all sample data records.
-
A test page in the Web API project will be rendered, indicating that the Web API data provider is ready to receive client calls.
It's recommended that you delete the existing database with the same name if you run the sample application again with a different ASP.NET version so that a fresh database will be initialized. Or you can do the migration task on the existing database. Otherwise, some column mapping errors may occur.
Library Projects
The legacy SM.Store.WebApi is an ASP.NET Web API 2 application with multi-layer .NET Framework class library structures.
When migrating to the ASP.NET Core, those projects must be converted to the .NET Core class library projects targeting to either .NET Core or .NET Standard framework. The project type .NET Standard could be used for more compatibility and flexibility. However, the folder structures and files are the same even if the project types are different. For the very original migrations to the .NET Core sample applications, the .NET Standard 2.x, NetStandard.Library, was used as the class library project type. For the sample applications with later versions of .NET Core, the class library project type is switched to the Microsoft.NETCore.App. The complete projects in the Visual Studio solution look like this:
Some details of migrating sample application from legacy ASP.NET Web API to ASP.NET Core are explained below:
-
The legacy SM.Store.Api.DAL, SM.Store.Api.BLL, and SM.Store.Api.Common projects were migrated to their corresponding projects with the same names.
-
The legacy SM.Store.Api.Entities and SM.Store.Api.Models were merged into the ASP.NET Core SM.Store.Api.Contracts project. All interfaces were also moved into this project which can be referenced by any other project but doesn’t have a reference to any other project in the solution.
-
The Web API Controller classes were moved from the SM.Store.Api project to the main .NET Core project, SM.Store.Api.Web. There is no need to separate those controller classes to another project targeting to the .NET Core.
-
Some needed libraries or components may not be automatically included in the project template. Thus, the missing items should also manually be added into the project from the NuGet. As an example for the sample applications with .NET Core 2x, the Microsoft.ASpNetCore.Mvc package is added into the SM.Store.Api.Common for being used by the custom model binder. For the .NET Core 3.x and above, the Microsoft.NetCore.App already contains the Microsoft.ASpNetCore.Mvc so that there is no need to explicitly reference it in the project.
There is no substantial change regarding the structures and files of the library projects in the Visual Studio solution between the ASP.NET Core versions. For the sample application, the framework reference types Microsoft.NetCore.App and NetStandard.Library, if used, can even be exchangeable for the different ASP.NET Core versions of the library projects.
Dependency Injections
Since the legacy SM.Store.WebApi application uses the Unity tool for the dependency injection(DI) logic and the new SM.Store.CoreApi has the ConfigurationServices routine in the Startup class ready for settings including DI, migrating the Unity to the Core built-in DI service is pretty straightforward. The custom code of the low-level DI Factory class and instance resolving method are no more required. The Unity container registrations can be replaced by the Core service configurations. For a comparison, I list below the setup code lines for the SM.Store.Api.DAL and SM.Store.Api.BLL objects in both legacy and new applications.
The type registration and mapping code in the Unity.config file of the legacy SM.Store.WebApi:
<container>
<register type="SM.Store.Api.DAL.IStoreDataUnitOfWork"
mapTo="SM.Store.Api.DAL.StoreDataUnitOfWork">
<lifetime type="singleton" />
</register>
<register type="SM.Store.Api.DAL.IGenericRepository[Category]"
mapTo="SM.Store.Api.DAL.GenericRepository[Category]"/>
<register type="SM.Store.Api.DAL.IGenericRepository[ProductStatusType]"
mapTo="SM.Store.Api.DAL.GenericRepository[ProductStatusType]"/>
<register type="SM.Store.Api.DAL.IProductRepository"
mapTo="SM.Store.Api.DAL.ProductRepository"/>
<register type="SM.Store.Api.DAL.IContactRepository"
mapTo="SM.Store.Api.DAL.ContactRepository"/>
<register type="SM.Store.Api.BLL.IProductBS"
mapTo="SM.Store.Api.BLL.ProductBS"/>
<register type="SM.Store.Api.BLL.IContactBS"
mapTo="SM.Store.Api.BLL.ContactBS"/>
<register type="SM.Store.Api.BLL.ILookupBS"
mapTo="SM.Store.Api.BLL.LookupBS"/>
</container>
The DI instance and type registrations in the Startup.ConfigureServices() method of the new SM.Store.CoreApi:
services.AddScoped(typeof(IGenericRepository<>), typeof(GenericRepository<>));
services.AddScoped(typeof(IStoreLookupRepository<>), typeof(StoreLookupRepository<>));
services.AddScoped<IProductRepository, ProductRepository>();
services.AddScoped<IContactRepository, ContactRepository>();
services.AddScoped<ILookupBS, LookupBS>();
services.AddScoped<IProductBS, ProductBS>();
services.AddScoped<IContactBS, ContactBS>();
Note that in the legacy SM.Store.WebApi, only the IStoreDataUnitOfWork type registration has the “singleton” lifetime manager. All other types use the default value which is the Transient lifetime for the registration. The StoreDataUnitOfWork object is outdated and not used by the new SM.Store.CoreApi (discussed in later section). All data-operative objects are now set as Scoped lifetime after the migration, which persists the object instances in the same request context.
There is also no change in the object instance injections to constructors or uses of the object instances from the callers, such as repository and business service classes. For the controller classes, the legacy SM.Store.WebApi calls the DI factory method to instantiate the object instance:
IProductBS bs = DIFactoryDesigntime.GetInstance<IProductBS>();
In the new SM.Store.CoreApi, similar functionality is performed by injecting an object instance to the controller’s constructor:
private IProductBS bs;
public ProductsController(IProductBS productBS)
{
bs = productBS;
}
Many third-party tools provide the static methods which we can also directly access from the ASP.NET Core. But for those that need one or more abstract layers, accessing the abstract layer instance through the DI is the ideal approach. The AutoMapper tool used in the sample application is an example. To make the AutoMapper work well with the ASP.NET Core DI container, the steps are given below:
-
Download the AutoMapper package through the Nuget.
-
Create the IAutoMapConverter interface:
public interface IAutoMapConverter<TSourceObj, TDestinationObj>
where TSourceObj : class
where TDestinationObj : class
{
TDestinationObj ConvertObject(TSourceObj srcObj);
List<TDestinationObj> ConvertObjectCollection(IEnumerable<TSourceObj> srcObj);
}
-
Add the code into the AutoMapConverter class:
public class AutoMapConverter<TSourceObj,
TDestinationObj> : IAutoMapConverter<TSourceObj, TDestinationObj>
where TSourceObj : class
where TDestinationObj : class
{
private AutoMapper.IMapper mapper;
public AutoMapConverter()
{
var config = new AutoMapper.MapperConfiguration(cfg =>
{
cfg.CreateMap<TSourceObj, TDestinationObj>();
});
mapper = config.CreateMapper();
}
public TDestinationObj ConvertObject(TSourceObj srcObj)
{
return mapper.Map<TSourceObj, TDestinationObj>(srcObj);
}
public List<TDestinationObj>
ConvertObjectCollection(IEnumerable<TSourceObj> srcObjList)
{
if (srcObjList == null) return null;
var destList = srcObjList.Select(item => this.ConvertObject(item));
return destList.ToList();
}
}
-
Add this instance registration line into the Startup.ConfigureServices() method:
services.AddScoped(typeof(IAutoMapConverter<,>), typeof(AutoMapConverter<,>));
-
Inject the AutoMapConverter instance into the caller class constructor:
private IAutoMapConverter<Entities.Contact, Models.Contact> mapEntityToModel;
public ContactsController
(IAutoMapConverter<Entities.Contact, Models.Contact> convertEntityToModel)
{
this.mapEntityToModel = convertEntityToModel;
}
-
Call a method in the initiated object instance (see ContactController.cs for details):
var convtList = mapEntityToModel.ConvertObjectCollection(rtnList);
The patterns and practices of the built-in dependent injections are basically the same across the .NET Core version 2.x through 5.0. No code change is needed for the newer .NET Core versions.
Accessing Application Settings
The .NET Core application uses more versatile configuration API. But for the ASP.NET Core application, setting and getting items from the AppSetting.json file is the prevailing option which is quite different from using the web.config XML file in the ASP.NET Web API application. If any configuration value is needed in the new SM.Store.CoreApi, there are two approaches to access the value after the Configuration object has been built.
-
Where the Configuration object with the IConfiguration or IConfigurationRoot type can be directly accessible, specify the Configuration array item, such as the code in the Startup.cs:
if (Configuration["AppConfig:UseInMemoryDatabase"] == "true")
{
services.AddDbContext<StoreDataContext>
(opt => opt.UseInMemoryDatabase("StoreDbMemory"));
}
else
{
services.AddDbContext<StoreDataContext>(c =>
c.UseSqlServer(Configuration.GetConnectionString("StoreDbConnection")));
}
-
Link the strong typed custom POCO class object to the Option service:
POCO class:
public class AppConfig
{
public string TestConfig1 { get; set; }
public bool UseInMemoryDatabase { get; set; }
}
Code in the Startup.ConfigurationServices():
services.AddOptions();
services.Configure<AppConfig>(Configuration.GetSection("AppConfig"));
Then access the config item via injecting the Option service instance to the constructor of caller classes.
private IOptions<AppConfig> config { get; set; }
public ProductsController(IOptions<AppConfig> appConfig)
{
config = appConfig;
}
var testConfig = config.TestConfig1;
What if the caller is from a static class in which no constructor is available? One of the resolutions is to change the static classes to regular ones. For migrating a legacy .NET Framework application having many static classes to the .NET Core application, however, these sort of changes plus related impacts could be very large.
The new SM.Store.CoreApi provides a utility class file, StaticConfigs.cs, to get any item value from the AppSettings.json file. The logic is to pass a configuration key name to the static method, GetConfig(), in which the same ConfigurationBuilder as in the Startup class is used to parse the JSON data and return the key’s value.
public static string GetConfig(string keyName)
{
var rtnValue = string.Empty;
var builder = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
.AddEnvironmentVariables();
IConfigurationRoot configuration = builder.Build();
var value = configuration["AppConfig:" + keyName];
if (!string.IsNullOrEmpty(value))
{
rtnValue = value;
}
return rtnValue;
}
This method can be called anywhere in the application. An example of calling this method is the code from the static class StoreDataInitializer in the SM.Store.Api.DAL project.
public static class StoreDataInitializer
{
public static void Initialize(StoreDataContext context)
{
if (StaticConfigs.GetConfig("UseInMemoryDatabase") != "true")
{
context.Database.EnsureCreated();
}
- - -
}
- - -
}
If you would like, an ASP.NET Core application can still use the web.config file for any legacy item from the appSettings XML section. However, the ConfigurationManager.AppSettings collection doesn’t work on the web.config in the ASP.NET Core which is a console application. To resolve this issue, the StaticConfigs.cs also contains the method, GetAppSetting(), for obtaining the AppSetting values from the web.config file in the Core project root:
public static string GetAppSetting(string keyName)
{
var rtnString = string.Empty;
var configPath = Path.Combine(Directory.GetCurrentDirectory(), "Web.config");
XmlDocument x = new XmlDocument();
x.Load(configPath);
XmlNodeList nodeList = x.SelectNodes("//appSettings/add");
foreach (XmlNode node in nodeList)
{
if (node.Attributes["key"].Value == keyName)
{
rtnString = node.Attributes["value"].Value;
break;
}
}
return rtnString;
}
Getting the configuration value is also a one-line call by passing the key name.
var testValue = StaticConfigs.GetAppSetting("TestWebConfig");
Entity Framework Core Related Changes
The SM.Store.CoreApi with the Entity Framework Core still uses the code-first workflow. The coding structures should be mostly the same when porting from the EF6 to EF Core 2.x through 5.0 for the application. Some issues and concerns, however, need to be taken care of regarding the changes of EF versions and behaviors.
If using the existing models for the EF Core projects, the manually seeded primary key values cannot be inserted due to the IDENTITY INSERT is set to ON in the database side by default. The EF6 automatically handles the issue, which turns off the identity insert if any key column is specified and value provided, or use the identity insert otherwise.
Take the ProductStatusType model for example. The below code works with the EF6:
public class ProductStatusType
{
[Key]
public int StatusCode { get; set; }
public string Description { get; set; }
public System.DateTime? AuditTime { get; set; }
public virtual ICollection<Product> Products { get; set; }
}
The data seeding array includes the StatusCode column and values:
var statusTypes = new ProductStatusType[]
{
new ProductStatusType { StatusCode = 1, Description = "Available",
AuditTime = Convert.ToDateTime("2017-08-26")},
new ProductStatusType { StatusCode = 2, Description = "Out of Stock",
AuditTime = Convert.ToDateTime("2017-09-26")},
- - -
};
With the EF Core, the DatabaseGeneratedOption.None needs to be explicitly added into the primary key attribute to avoid the failure resulting from explicitly providing the key and value. The above model should be updated like this:
public class ProductStatusType
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int StatusCode { get; set; }
public string Description { get; set; }
- - -
}
The legacy SM.Store.WebApi with the EF6 passes the connection string to the data context class like this:
public class StoreDataContext : DbContext
{
public StoreDataContext(string connectionString)
: base(connectionString)
{
}
- - -
}
The EF Core passes the DbContextOption object to the data context class. This would be a minor change in the class.
public class StoreDataContext : DbContext
{
public StoreDataContext(DbContextOptions<StoreDataContext> options)
: base(options)
{
}
- - -
}
The DbContextOption items need to be specified when adding the data context into the DI container in the Startup.ConfigurationServices(). With this EF Core feature, we can use different data providers and database operation-related settings. In this sample application, either the in-memory or the SQL Server database can be enabled with the configuration settings.
if (Configuration["AppConfig:UseInMemoryDatabase"] == "true")
{
services.AddDbContext<StoreDataContext>(opt => opt.UseInMemoryDatabase("StoreDbMemory"));
}
else
{
services.AddDbContext<StoreDataContext>(c =>
c.UseSqlServer(Configuration.GetConnectionString("StoreDbConnection")));
}
The value of the SQL Server connection string for the SM.Store.CoreApi is configured in the standard ConnectionStrings section of the appsettings.json file. The database files are saved to the Windows login user folder for the LocalDB and the SQL Server defined data folder for any regular edition including the SQL Server Express. No option of LocalDB database file location can be set in the connection string as the EF6 does. If you need to specify a different file location for the LocalDB, you can open the LocalDB instance with the SQL Server Management Studio (SSMS, free version 18.x or the latest) and so either below options:
-
For existing database, right click the database instance in the Object Explorer > Properties > Database Settings. Change the paths of the database files under the Database default locations section.
-
For creating the new database, execute the script before running the sample application for the first time or after deleting the existing database.
USE MASTER
GO
CREATE DATABASE [StoreCF8]
ON (NAME = 'StoreCF8.mdf', FILENAME = <your path>\StoreCF8.mdf')
LOG ON (NAME = 'StoreCF8_log.ldf', FILENAME = <your path>\StoreCF8_log.ldf');
GO
Although Microsoft claims that the DbContext instance combines both Repository and Unit of Work patterns, custom repositories are still a good abstraction layer between the DAL and BLL of a multi-layer application. All repository files in the SM.Store.Api.DAL project should have no major changes when migrating from the legacy SM.Store.WebApi with the EF6 to the SM.Store.CoreApi with the EF Core 2.x through 5.0. Some updates are made due simply to outdated coding structures which should have already been corrected in the legacy application with the EF6:
-
Removing UnitOfWork class. The legacy SM.Store.WebApi wraps any repository class with the UnitOfWork class like this:
private StoreDataContext context;
public class StoreDataUnitOfWork : IStoreDataUnitOfWork
{
public StoreDataUnitOfWork(string connectionString)
{
- - -
this.context = new StoreDataContext(connectionString);
}
- - -
public void Commit()
{
this.Context.SaveChanges();
}
- - -
}
The UnitOfWork instance is then injected into any individual repository and the base GenericRepository:
public class ProductRepository : GenericRepository<Entities.Product>, IProductRepository
{
public ProductRepository(IStoreDataUnitOfWork unitOfWork)
: base(unitOfWork)
{
}
- - -
}
Although custom repositories are still needed and migrated, the unit-of-work practice seems redundant for applications (even with EF6). The data context class itself acts as the unit-of-work in which the SaveChanges() method updates pending changes in the current context all at once. In addition, for an application with multiple data context classes, we can use transaction related methods in the context’s Database object, such as UseTransaction, BeginTransaction, and CommitTransaction, to achieve the ACID results.
In the SM.Store.CoreApi, the IUnitOfWork interface and UnitOfWork class no more exist. The StoreDataContext instance is directly injected into repository classes:
public class ProductRepository : GenericRepository<Entities.Product>, IProductRepository
{
private StoreDataContext storeDBContext;
public ProductRepository(StoreDataContext context)
: base(context)
{
storeDBContext = context;
}
- - -
}
In the GenericRepository class, the CommitAllChanges() method is replaced with the new Commit() method:
Method in the obsolete UnitOfWork class:
public virtual void CommitAllChanges()
{
this.UnitOfWork.Commit();
}
Method in the new GenericRepository class:
public virtual void Commit()
{
Context.SaveChanges();
}
Moreover, any base Insert, Update, or Delete method in the GenericRepository class has the optional second argument to call the SaveChanges() method immediately if you would not like to call the Commit() method till last. Here is the Insert method, for example:
public virtual object Insert(TEntity entity, bool saveChanges = false)
{
var rtn = this.DbSet.Add(entity);
if (saveChanges)
{
Context.SaveChanges();
}
return rtn;
}
-
Making GenericRepository real generic. The GenericRepository class from the legacy Web API DAL only works on single data context since it receives the derived StoreDataContext instance passed via the StoreDataUnitOfWork.
public class GenericRepository<TEntity> :
IGenericRepository<TEntity> where TEntity : class
{
public IStoreDataUnitOfWork UnitOfWork { get; set; }
public GenericRepository(IStoreDataUnitOfWork unitOfWork)
{
this.UnitOfWork = unitOfWork;
}
- - -
}
Hence, other generic repository classes with different names need to be created if there are multiple data context objects. In the GenericRepository class from the .NET Core DAL, the base DbContext is now injected into its constructor, allowing it to be used by any inheriting repository of other data context objects.
public class GenericRepository<TEntity> :
IGenericRepository<TEntity> where TEntity : class
{
private DbContext Context { get; set; }
public GenericRepository(DbContext context)
{
Context = context;
}
- - -
}
With such a change, all of the associated workflows run fine except for directly using the GenericRepository instance to obtain the simple lookup data sets. The legacy Web API code in the SM.Store.Api.Bll/LookupBS.cs file looks like this:
private IGenericRepository<Entities.Category> _categoryRepository;
private IGenericRepository<Entities.ProductStatusType> _productStatusTypeRepository;
public LookupBS(IGenericRepository<Entities.Category> cateoryRepository,
IGenericRepository<Entities.ProductStatusType> productStatusTypeRepository)
{
this._categoryRepository = cateoryRepository;
this._productStatusTypeRepository = productStatusTypeRepository;
}
The GenericRepository instance from the .NET Core DAL cannot be directly used by the classes in the BLL project since it needs an instantiated data context, StoreDbContext, not the base DbContext, to be injected into the GenericRepository. To keep almost the same code lines in the LookupBS.cs, we need the new IStoreLookupRepository.cs with empty member and StoreLookupRepository.cs with the code only for its constructor:
public interface IStoreLookupRepository<TEntity> : IGenericRepository<TEntity>
where TEntity : class
{
}
public class StoreLookupRepository<TEntity> : GenericRepository<TEntity>,
IStoreLookupRepository<TEntity> where TEntity : class
{
public StoreLookupRepository(StoreDataContext context)
: base(context)
{
}
}
Then in the LookupBS.cs, simply replace the text “GenericRepository” with the “StoreLookupRepository”. It’s now working the same as before the migration from ASP.NET Web API to the ASP.NET Core.
The code implementation for the GenericRepository is kept the same when migrating the .NET Core and EF Core version from 2x through 5.0 for the application.
-
Updating to Async methods if available. The group of Async methods has already been provided in the EF6. The SM.Store.WebApi doesn’t use any due to early implementations. My plan of the migration includes the work on updating the methods to perform asynchronous operations whenever possible in the SM.Store.CoreApi application. Audiences can look into the project files for detailed changes but the outlines of the changes are listed here.
- Adding another set of methods with the
Async operations in the GenericRepository. - Changing the existing methods to
Async operations for all possible processes. - Making related changes in the BLL and API controller code accordingly.
There is an exception. The Async approach doesn't support any method with an output argument. Thus, all methods with any output argument in the new DAL, BLL, and API controllers have still been kept the non-async originals.
The EF Core 3.x poses many breaking changes over the EF Core 2x, in which the most prominent one is to remove the LINQ evaluation on the client. Although this increases the data access performance and reduces the chance of SQL injection, migrating an application with the EF from previous version to Core 3.x and above may need tremendous code changes and tests, especially for any large enterprise data application using a large number of LINQ-to-SQL queries.
In the sample application, the major LINQ query for getting the sorted and paginated data list uses the GroupJoin and SelectMany with lambda expressions works fine with the EF Core 2.x but not 3.x and above. The below IQueryable code cannot be converted to the correct SQL query and therefore renders the error message "NavigationExpandingExpressionVisitor failed".
var query = storeDBContext.Products
.GroupJoin(storeDBContext.Categories,
p => p.CategoryId, c => c.CategoryId,
(p, c) => new { p, c })
.GroupJoin(storeDBContext.ProductStatusTypes,
p1 => p1.p.StatusCode, s => s.StatusCode,
(p1, s) => new { p1, s })
.SelectMany(p2 => p2.s.DefaultIfEmpty(), (p2, s2) => new { p2 = p2.p1, s2 = s2 })
.Select(f => new Models.ProductCM
{
ProductId = f.p2.p.ProductId,
ProductName = f.p2.p.ProductName,
CategoryId = f.p2.p.CategoryId,
CategoryName = f.p2.p.Category.CategoryName,
UnitPrice = f.p2.p.UnitPrice,
StatusCode = f.p2.p.StatusCode,
StatusDescription = f.s2.Description,
AvailableSince = f.p2.p.AvailableSince
});
The code is working for the EF Core 3.x and above when the LINQ expression is changed to using the "from...join...select" syntax with the SQL OUTTER JOIN scenario.
var query =
from pr in storeDBContext.Products
join ca in storeDBContext.Categories
on pr.CategoryId equals ca.CategoryId
join ps in storeDBContext.ProductStatusTypes
on pr.StatusCode equals ps.StatusCode into tempJoin
from t2 in tempJoin.DefaultIfEmpty()
select new Models.ProductCM
{
ProductId = pr.ProductId,
ProductName = pr.ProductName,
CategoryId = pr.CategoryId,
CategoryName = ca.CategoryName,
UnitPrice = pr.UnitPrice,
StatusCode = pr.StatusCode,
StatusDescription = t2.Description,
AvailableSince = pr.AvailableSince
};
This change also impacts the other parts of the code. In the SM.Store.Api.Common/GenericSorterPager.cs and SM.Store.Api.Common/GenericMultiSorterPager.cs, the exiting code in the GetSortedPagedList() method calls the IQureryable.Distinct() method for returning the distinctive data items in the resultset.
public static IList<T> GetSortedPagedList<T>(IQueryable<T> source, PaginationRequest paging)
{
- - -
source = source.Distinct();
- - -
}
The change to using the "from...join...select" syntax requires for adding the default OrderBy to the LINQ query. Otherwise, it will render the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified" if any call contains no sorting request. The below code piece needs to be added into the IQueryable source that passes to the GetSortedPagedList() method call to sort the select list by the primary key as default setting.
if (paging != null && (paging.Sort == null || string.IsNullOrEmpty(paging.Sort.SortBy)))
{
paging.Sort = new Models.Sort() {SortBy = "ProductId"};
}
Different EF versions support different methods to execute stored procedures with the EF data context although the same SqlParameter items are used as the method arguments. The code pieces of the sample applications demonstrate the details. Also note that the method used for stored procedure execution is actually to execute any raw SQL script.
-
EF6: The existing SM.Store.WebApi directly uses the Database.SqlQuery<T>() method to execute the GetPagedProductList stored procedure.
var result = this.Database.SqlQuery<Models.ProductCM>("dbo.GetPagedProductList " +
"@FilterString, @SortString, @PageNumber, @PageSize, @TotalCount OUT",
_filterString, _sortString, _pageNumber, _pageSize, _totalCount).ToList();
-
Core 2.0: [Note: This version has not been supported by Microsoft so that I remove the source code in the ASP.NET Core 2.0 with the current update. The approach of executing a stored procedure in the Core 2.0 is still kept here for history references]
The Database.SqlQuery method is not supported by the EF Core. As an alternative, we can use the FromSql method for the Dbset<T>. To implement this approach, the below steps are needed.
-
Create a property with the Dbset<T> type. The dynamic model is the stored procedure return type.
public DbSet<Models.ProductCM> ProductCM_List { get; set; }
-
Modify the POCO model with attributes NotMapped and Key. Especially for the Key, if not set, it would render the runtime error "The entity type 'ProductCM' requires a primary key to be defined".
[NotMapped]
public partial class ProductCM
{
[Key]
public int ProductId { get; set; }
public string ProductName { get; set; }
- - -
}
-
Then use the FromSql method of the Dbset property to execute the stored procedure.
var result = this.ProductCM_List.FromSql("dbo.GetPagedProductList " +
"@FilterString, @SortString, @PageNumber, @PageSize, @TotalCount OUT",
_filterString, _sortString, _pageNumber, _pageSize, _totalCount).ToList();
-
Core 2.1: [Note: Also for Core 2.2 which has not been supported by Microsoft.]
The Core 2.1 introduced the DbContext.Query method of which the same FromSql method can be called to execute stored procedures. For the best code practice, the Query<T>() with the return type would also be defined in the OnModelCreating method.
protected override void OnModelCreating(ModelBuilder builder)
{
- - -
builder.Query<Models.ProductCM>();
}
Then the calling stored procedure method in the GetProductListSp() uses the Query<T>() like this:
var result = this.Query<Models.ProductCM>().FromSql("dbo.GetPagedProductList " +
"@FilterString, @SortString, @PageNumber, @PageSize, @TotalCount OUT",
_filterString, _sortString, _pageNumber, _pageSize, _totalCount).ToList();
-
Core 3.x and above: The Query<T>() is removed and the Dbset<T> is back to the play. The FromSqlRaw() or FromSqlInterpolated() replaces the FromSql() for executing the stored procedures.
var result = this.ProductCMs.FromSqlRaw("dbo.GetPagedProductList " +
"@FilterString, @SortString, @PageNumber, @PageSize, @TotalCount OUT",
_filterString, _sortString, _pageNumber, _pageSize, _totalCount).ToList();
The ProductCMs is declared as the Dbset<T> property without the PK key.
public DbSet<Models.ProductCM> ProductCMs { get; set; }
- - -
protected override void OnModelCreating(ModelBuilder builder)
{
- - -
builder.Entity<Models.ProductCM>().HasNoKey();
}
Although it's working, I still have two negative comments on the approach of stored procedure executions with the EF Core 3.x and above.
-
There is no way to avoid creating the physical table for the DbSet<T> property. Using the Ignore(), [NotMapped] attribute, or builder.Entity<T>.ToTable(null) are all not relevant. I have to manually delete the table (ProductCM in this case) from the database initiated or migrated.
-
The syntax of the FromSqlInterpolated() method is nice but it doesn't support executing any stored procedure that has any output parameter. Using the FromSqlRaw() with the SqlParameter objects works though.
Custom Model Binder
I previously shared my work on a custom model binder for passing complex hierarchical object in a query string to ASP.NET Web API methods. When I copied the file, FieldValueModelBinder.cs, to the ASP.NET Core library project, SM.Store.Api.Common, and resolved all references, errors still occurred. The IModelBinder interface type comes from the Microsoft.AspNetCore.Mvc.ModelBinding namespace whereas it previously was a member of the System.Web.Http.ModelBinding. It's a major change since the HttpContext is composed by a set of new request features, which breaks the compatibility to the ASP.NET Web API version.
Fortunately, I could re-map the objects, properties, and methods to the new available ones. In addition, the only implemented method, BindModel(), would be switched to the asynchronous type, BindModelAsync(), with return type as the Task.
Here is the BindModel() method in the SM.Store.WebApi with the .NET Framework 4x.
public bool BindModel(HttpActionContext actionContext, ModelBindingContext bindingContext)
{
if (!string.IsNullOrEmpty(actionContext.Request.RequestUri.Query))
{
kvps = actionContext.Request.GetQueryNameValuePairs().ToList();
}
else if (actionContext.Request.Content.IsFormData())
{
var bodyString = actionContext.Request.Content.ReadAsStringAsync().Result;
try
{
kvps = ConvertToKvps(bodyString);
}
catch (Exception ex)
{
bindingContext.ModelState.AddModelError(bindingContext.ModelName, ex.Message);
return false;
}
}
else
{
bindingContext.ModelState.AddModelError(bindingContext.ModelName, "No input data");
return false;
}
var obj = Activator.CreateInstance(bindingContext.ModelType);
try
{
SetPropertyValues(obj);
}
catch (Exception ex)
{
bindingContext.ModelState.AddModelError(
bindingContext.ModelName, ex.Message);
return false;
}
bindingContext.Model = obj;
return true;
}
The BindModeAsync() method in the new SM.Store.CoreApi seems more concise than the ASP.NET Web API version:
public Task BindModelAsync(ModelBindingContext bindingContext)
{
if (bindingContext.HttpContext.Request.QueryString != null)
{
kvps = bindingContext.ActionContext.HttpContext.Request.Query.ToList();
}
else if (bindingContext.HttpContext.Request.Form != null)
{
try
{
kvps = bindingContext.ActionContext.HttpContext.Request.Form.ToList();
}
catch (Exception ex)
{
bindingContext.ModelState.AddModelError(bindingContext.ModelName, ex.Message);
}
}
else
{
bindingContext.ModelState.AddModelError(bindingContext.ModelName, "No input data");
}
var obj = Activator.CreateInstance(bindingContext.ModelType);
try
{
SetPropertyValues(obj);
bindingContext.Result = ModelBindingResult.Success(obj);
}
catch (Exception ex)
{
bindingContext.ModelState.AddModelError(
bindingContext.ModelName, ex.Message);
}
return Task.CompletedTask;
}
The KeyValuePair (kvps) type returned from the …Request.Query.ToList() and …Request.Form.ToList() is now the List<KeyValuePair<string, StringValues>> instead of List<KeyValuePair<string, string>>. Thus, any related reference and code line need to be changed accordingly, mostly for the object declarations and assignments:
List<KeyValuePair<string, StringValues>> kvpsWork;
- - -
kvpsWork = new List<KeyValuePair<string, StringValues>>(kvps);
After making those changes, everything of the ASP.NET Core model binder works the same as the legacy version. More test cases are provided in the file, TestCasesForDataServices.txt included in the downloadable AspNetCore5.0_DataService. You can enter any URL with the query string into the request input area of the Postman, and then click Send button. The complex object structures and values based on the query string will be shown in the response section.
The above test case shows a multiple-column sorting scenario that is supported by the custom FieldValueModelBinder in the ASP.NET Core sample applications. More test cases like the below URL are provided in the TestCasesForDataServices.txt file. You may need to change the port number in the URLs to point to the correct website you set.
http:
ProductSearchField=CategoryID&ProductSearchFilter[0]ProductSearchText=2&PaginationRequest[0]
PageIndex=0&PaginationRequest[0]PageSize=10&PaginationRequest[0]SortList[0]SortItem[0]
SortBy=StatusDescription&PaginationRequest[0]SortList[0]SortItem[0]
SortDirection=desc&PaginationRequest[0]SortList[1]SortItem[1]
SortBy=ProductName&PaginationRequest[0]SortList[1]SortItem[1]SortDirection=asc
The structures and code of the FieldValueModelBinder are compatible across the .NET Core versions 2x through 5.0. There is no breaking change between these versions.
Using IIS Express and Local IIS
One of the prominent changes from the ASP.NET Web API to the ASP.NET Core is the application output and host types even though I’m only concerned about the applications running in the Windows systems. The migrated SM.Store.CoreApi in ASP.NET Core version 2.1 (actually any .NET Core version before 2.2) is the out-process-only console application that runs on the built-in Kestrel web server by default. We can still use the IIS Express as a wrapper for the development environment especially with the Visual Studio. We can also use the IIS as a reverse proxy to relay the requests and responses for all environments. Behind the scene, a structure called ASP.NET Core Module plays rolls in managing all processes and coordinating functionalities from the IIS/IIS Express and Kestrel web server. The ASP.NET Core Module is automatically installed with the Visual Studio 2017/2019 installation on the development machine.
The in-process hosting when using the IIS is available in the ASP.NET Core 3.x and above (as an option in the ASP.NET Core 2.2). If you download and open the sample application in the ASP.NET Core 3.1 or 5.0, and use the steps mentioned below to setup the local IIS website, you can already run the site with the IIS in-process hosting. However, you need to install ASP.NET Core Hosting Bundle for version 3.1 or 5.0.
When starting the legacy SM.Store.WebApi within the Visual Studio 2017/2019, the sample application runs the website under the IIS Express process. You can also easily start the Web API by executing the IIS Express with command lines or a batch file.
"C:\Program Files\IIS Express\iisexpress.exe" /site:SM.Store.Api.Web
/config:"<your <code>SM.Store.WebApi</code> path>\.vs\config\applicationhost.config"
The same command line execution of the IIS Express doesn’t work for the SM.Store.CoreApi application since it runs in a console application process separate from the IIS Express worker process. If you would like to keep the SM.Store.CoreApi and IIS Express running for providing data services to multiple clients in the development environment, simply follow these steps:
- Open the
SM.Store.CoreApi with the Visual Studio 2017/2019 instance. - Press Ctrl + F5. The starting page will be shown in the selected browser.
- Close the browser and minimize the Visual Studio instance.
- The IIS Express is now running in the Windows’ background for receiving any HTTP request from client calls.
If more stable and persistent data services are needed on the development machine, you can publish the SM.Store.CoreApi to the local IIS using the approaches similar to those for the traditional ASP.NET website or Web API applications. These are major setup steps:
-
Open the SM.Store.CoreApi in the Visual Studio 2017/2019 and highlight the solution, select Publish SM.Store.CoreApi from the Build menu, select Folder as the publishing target, specify your folder path for your Folder Profile, and then click Publish.
-
Open the IIS manager (inetmgr.exe), select Application Pools and then Add Application Pool…, enter the name StoreCorePool, and select the No Managed Code from the .NET CLR Version dropdown.
-
Right click Sites/Default Web Site, and select Add Application. Enter the StoreCore as Alias, select the StoreCorePool from the Application pool dropdown, and then enter (or browse to) your folder path that holds the published application files.
-
Right click the Default Web Site, select Manage Website and then Restart. Since the SM.Store.CoreApi application uses the in-memory database as the initial setting, you can now access the data service methods using any client tool with the URL http://localhost/storecore/api/<method-name>.
Note that the application pool name is no more the application running process identity so that it cannot be passed as an authorization account to access other resources from the application. For example, if you try to access the data in your local SQL Server or SQL Server Express instance from the SM.Store.CoreApi with the local IIS using the “integrated security=True” or “Trusted_Connection=True”, you will get the SQL Server access permission error
If you need to run the SM.Store.CoreApi application with the local IIS and the SQL Server database instead of using the in-memory database that is only for simple test cases and doesn't support stored procedures, there are two options:
-
Mapping the application pool account, IIS AppPool\StoreCorePool, to the SQL Server login and user. You can then use the existing database connection string in the data service application with the integreted security.
- Open the Object Explorer in the SSMS tool, and then expand the Security.
- Right click Logins > New Login...
- Enter
IIS AppPool\StoreCorePool as Login name. Do not click Search... button. - Select the "StoreCF8" (or your database name) from the Default database dropdown list.
- Open User Mapping on the upper left panel, and select the "StoreCF8" (or your database name).
- Select the "db datareader" and "db datawriter" in the Database role membership for: panel.
- Back to the Object Explorer.
- Right-click the StoreCF8 database > Properties > Permissions.
- Highlight the
IIS APPPOOL\StoreCorePool on the Users or roles panel. - Check the Grant box for the Execute from the Explicit Permissions list.
-
Creating a specific SQL Server user for the login and role mapping, and also granting the execute permission. You can run the script with the SSMS:
USE master
GO
CREATE LOGIN WebUser WITH PASSWORD = 'password123',
DEFAULT_DATABASE = [StoreCF8],
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF;
GO
USE StoreCF8
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'WebUser')
BEGIN
CREATE USER [WebUser] FOR LOGIN [WebUser]
EXEC sp_addrolemember N'db_datareader', N'WebUser'
EXEC sp_addrolemember N'db_datawriter', N'WebUser'
EXEC sp_addrolemember N'db_ddladmin', N'WebUser'
END;
GO
GRANT EXECUTE TO WebUser;
GO
The above script is included in the StoreCF8.sql file from the downloaded source. You can actually run the entire script in this file to create the SQL Server database with the login user and then enable or update the connection string in the appsettings.json file under the published folder of SM.Store.CoreApi for the SQL Server instance:
"ConnectionStrings": {
"StoreDbConnection": "Server=<your SQL Server instance>;Database=StoreCF8;
User Id=WebUser;Password=password123;MultipleActiveResultSets=true;"
}
With either of the above options, you also need to remove this line for the in-memory database in the appsettings.json file (or replace the value "true" with "false"):
"UseInMemoryDatabase": "true"
The SM.Store.CoreApi application with local IIS and SQL Server database should now be working on your local machine.
Summary
Migrating a data service application to higher tool or framework versions needs efforts on rewriting the code and solving more or less problems in respect to project types, settings, built-in tools, workflow, running processes, hosting schemes, etc. The samples, code, and discussions in this article can help developers catch up essences of the migration tasks on different versions of the ASP.NET applications and also speed up the code work on newer versions of the ASP.NET Core applications.
History
-
10th January, 2018
-
17th January, 2018
-
2nd August, 2018
-
1st March, 2019
- Upgraded source code of the sample application with the ASP.NET Core 2.2
- Updated text in some sections
-
7th June, 2019
- Added the sub-section of Executing Stored Procedures
- Removed the section of Enable CORS for Localhost (no cross-domain issue for different localhost ports with later versions of VS 2017 and VS 2019)
- Edited text in several sections
- Updated the source code files. All types of the sample application projects now support the multiple-column sorting for paginated data list.
-
27th November, 2019
- Updated the sample application with the ASP.NET Core 3.0
- Added sections and paragraphs into the article for the ASP.NET Core 3.0
- Edited existing text in all sections
-
29th November, 2020
- Updated the sample application with the ASP.NET Core 5.0 and 3.1
- Updated the downloadable source code zip list to those with .NET versions currently supported by Microsoft (.NET 5, .NET Core 3.1, .NET Core 2.1, and .NET Framework 4x)
- Edited existing text in sections related to the changes with the ASP.NET Core 5.0 and 3.1
Shenwei is a software developer and architect, and has been working on business applications using Microsoft and Oracle technologies since 1996. He obtained Microsoft Certified Systems Engineer (MCSE) in 1998 and Microsoft Certified Solution Developer (MCSD) in 1999. He has experience in ASP.NET, C#, Visual Basic, Windows and Web Services, Silverlight, WPF, JavaScript/AJAX, HTML, SQL Server, and Oracle.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
