How to Use String Keyword as an Entity in a LINQ Query
Today we will talk about how to use string parameters as an Entity on Linq Query with .NET 8.
First of all, I want to explain why I need such a thing. I was writing a financial application. Every new year, customer tables were backed up with the name of the previous year, and if necessary, user information from previous years was also pulled from these backed-up tables. “User_2023”, “User_2022”, “User”=> Current year. So we could not update the code every year such as adding a new case to the switch statements :) We had to do all things dynamically.
Create .Net 8 WebApi Application
Firstly we will create an Entities folder. We will create all entities from the existing MsSqlDB using the scaffold tool. If you don’t have the scaffold tool, you have to install it using the command below. It is a .Net 8.0 version.
dotnet add package Microsoft.EntityFrameworkCore.Tools — version 8.0.7
Life is not a permanent condition. It is a dynamic process of constant change and growth.
— Sandra Brossman
We will create all entities from the ABYS_PROD Database’s Tables with the below command. You can run this command under Package Manager Console.
dotnet ef dbcontext scaffold "Data Source=192.168.50.173;
Initial Catalog=ABYS_PROD;Persist Security Info=True;User ID=sa;
Password=**yourPassword**;pooling=True;min pool size=0;max pool size=100;
MultipleActiveResultSets=True;TrustServerCertificate=True;"
Microsoft.EntityFrameworkCore.SqlServer -o Entities
--context-dir "Entities\DbContexts" --no-pluralize -c
DashboardContext -f --no-buildMsSqlDB ABYS_PROD: This is our test DB. There are a couple of DB_USER table clones by year.
After we run the “ef dotnetcontext scaffold” command, the entity folder and all pocos will be generated as below.
Everything is ready. Let’s write first UserWebApi
1-) Add a connection string to the appsettings.json. For safety, it is better to write the connection string as encrypted.
"ConnectionStrings": {
"DefaultConnection": "Data Source=192.168.50.173;
initial catalog=ABYS_PROD;User Id=sa;Password=**yourPassword**;
TrustServerCertificate=True",
},2-) Add DBContext to the program.cs
builder.Services.AddDbContext<DashboardContext>(options =>
options.UseSqlServer(builder.Configuration["DefaultConnection"]));3-) Create User ViewModel. We don’t get all user columns. We only get the properties we want to show in the UI.
namespace LinqToDBBlog.Models
{
public partial class CustomUserModel
{
public string Name { get; set; }
public string LastName { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public string Email { get; set; }
public string Gsm { get; set; }
public bool? IsAdmin { get; set; }
public string SecurityRoleName { get; set; }
public int? IdSecurityRole { get; set; }
public int IdUser { get; set; }
public DateTime CreDate { get; set; }
}
}4-) Firstly we will create a classic get all users webApi from DBUser.
using LinqToDBBlog.Entities;
using LinqToDBBlog.Entities.DbContexts;
using LinqToDBBlog.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
namespace LinqToDBBlog.Controllers
{
[ApiController]
[Route("[controller]")]
public class LinqToDbController : ControllerBase
{
private readonly DashboardContext _context;
public LinqToDbController(DashboardContext context)
{
_context = context;
}
[HttpGet("GetUserList")]
public List<CustomUserModel> Get()
{
var users = from user in _context.DbUser
join role in _context.DbSecurityRole on user.IdSecurityRole
equals role.IdSecurityRole into roleLeft
from role in roleLeft.DefaultIfEmpty()
select new CustomUserModel
{
Name = user.Name,
LastName = user.LastName,
UserName = user.UserName,
Password = user.Password,
Email = user.Email,
Gsm = user.Gsm,
IsAdmin = user.IsAdmin,
SecurityRoleName = role.SecurityRoleName,
IdSecurityRole = role.IdSecurityRole,
IdUser = user.IdUser,
CreDate = user.CreDate
};
return users.ToList();
}
}
}Change is not an event, it’s a process.
— CHERYL JAMES
This is the Swagger GetUserList() method Result: We use a standart Linq query here and get User Details and Roles.
5-) Now Let’s make DBUser entity as a dynamic:
Firstly install the “linq2db” library from Nuget. And other EntityFramework Libraries as below.
Sometimes one creates a dynamic impression by saying something, and sometimes one creates as significant an impression by remaining silent.
— Dalai Lama
With “ToLinqToDBTable() .TableName(tableName)” methods, we can convert string name to entity. Of course, we have to give the existing table name to the tableName parameter. Otherwise, we will get an error from the Linq Query. And other parts are the same.
[HttpGet("GetUserListFromTableName/{tableName}")]
public List<CustomUserModel> GetFromTableName(string tableName)
{
var users = from user in _context.Set<DbUser>().ToLinqToDBTable()
.TableName(tableName)
join role in _context.DbSecurityRole on user.IdSecurityRole
equals role.IdSecurityRole into roleLeft
from role in roleLeft.DefaultIfEmpty()
select new CustomUserModel
{
Name = user.Name,
LastName = user.LastName,
UserName = user.UserName,
Password = user.Password,
Email = user.Email,
Gsm = user.Gsm,
IsAdmin = user.IsAdmin,
SecurityRoleName = role.SecurityRoleName,
IdSecurityRole = role.IdSecurityRole,
IdUser = user.IdUser,
CreDate = user.CreDate
};
return users.ToList();
}
}Swagger Result: We will get 2019 years of User data from this request.
6-) You can create a combo to get all user tables. And you can write as below SqlQuery to fill the combo. If you select the user tableName parameter from this combo, there is no chance of making mistakes.
SELECT table_name
FROM information_schema.tables
WHERE table_name LIKE 'DB_USER%';
A change is as good as a rest.
— STEPHEN KING
7-) Write Same Query With Linq Extensions:
Here again, we take the string table name as parametric and this time write the same query in a different way with Linq Extension and get the same result.
[HttpGet("GetUserListFromTableNameWithExtension/{tableName}")]
public List<CustomUserModel> GetFromTableNameWithExtension(string tableName)
{
var users = _context.Set<DbUser>().ToLinqToDBTable().TableName(tableName)
.Where(u => u.Deleted != true)
.GroupJoin(
_context.DbSecurityRole,
u => u.IdSecurityRole,
r => r.IdSecurityRole,
(u, roles) => new { u, roles }
)
.SelectMany(
ur => ur.roles.DefaultIfEmpty(),
(ur, r) => new CustomUserModel
{
Name = ur.u.Name,
LastName = ur.u.LastName,
UserName = ur.u.UserName,
Password = ur.u.Password,
Email = ur.u.Email,
Gsm = ur.u.Gsm,
IsAdmin = ur.u.IsAdmin,
SecurityRoleName = r.SecurityRoleName,
IdSecurityRole = r.IdSecurityRole,
IdUser = ur.u.IdUser,
CreDate = ur.u.CreDate
}
);
return users.ToList();
}
Linq Query and Linq Extension actually are the same. You can use whatever is easiest for you. I wanted to give you both examples in this article.
Conclusion:
In this article, I wanted to show how can we create a LINQ query dynamically with string entity parameters. If our entities change or extend in time we can not modify our queries manually forever. We have to adapt our codes to the changes dynamically. So in this article, I want to show you the Linq2DB tool. It is very handy and extensible. Hopefully, it helps you if you are in kind of situation.
See you until the next article.
“If you have read so far, first of all, thank you for your patience and support. I welcome all of you to my blog for more!”
Source [Github]: https://github.com/borakasmer/LinqToDB
