Special Solutions for Some Weaknesses of Entity Framework
Today I would like to talk about my difficulties while querying Raw SQL on Entity Framework. Firstly with .Net 6.0, you can do everything with Linq Query. But what if you need Raw SQL? For Example, if you need SAP Integration, you use probably Hana Library. And Hana is not supported .Net 6.0 with Linq query yet. So you have to write Raw SQL.
In the Below example, you can see, some simple Custom Query. The results come from the “User” and “UserAddress” tables. Entity Framework needs a specific model for the result of a query “DbSet<T>.SqlQuery()”. But what if your query result returns not a DB Table? If it returns a custom model from two or more tables like below. Before In .Net Core 2.0, you could use “dbData.Database.SqlQuery<SomeModel>” without any type of declaration. But after .Net Core 3.1, it is unfortunately impossible.
DbSet<TEntity>.SqlQuery() => DbSet<CustomUserVirtualDBModel>.SqlQuery()
using (VirtualFlorenceContext context = new())
{
string rawSql = "SELECT U.Name,U.Surname, ua.Address FROM Users AS u " + "INNER JOIN[dbo].[UserAddres] AS ua ON U.ID = ua.UserId " +
"WHERE u.BirthDate IS NOT NULL"; DbSet<CustomUserVirtualDBModel> entites = context.Set<CustomUserVirtualDBModel>(); List<CustomUserVirtualDBModel> data = entites.FromSqlRaw(rawSql).ToList();
}
1. Solution: You can create a View on SqlDB “CustomUserVirtualDBModel” as seen below. Of course, this is not my first choice but you can use “View” as an “Entity” in your “FromSqlRaw()” method. After all you can use rawSql with your ViewModel as seen above. But this is insane I think. Why because we have to create a View on DB for every custom model.
If you create the “CustomUserVirtualDBModel” View on SQLDB and if you don’t have to write rawSQL, you can write a simple Linq Query as seen below. This is extra bonus information!
“Life is filled with difficult decisions, and winners are those who make them.” — Dan Brown
var result = (from u in Users
join ua in UserAddres on u.ID equals ua.UserId
where u.BirthDate != null
select new CustomUserVirtualDBModel
{
Name = u.Name,
Surname = u.Surname,
Address = ua.Address
}).ToList();2. Solution: You can create a ViewModel class and add it to the DBContext as if there is a table like this in SqlDB. This is how we will fool DBContext.
This is our ViewModel:
namespace DAL.VirtualModels
{
public partial class CustomUserVirtualDBModel
{
public string Name { get; set; }
public string Surname { get; set; }
public string Address { get; set; }
}
}VirtualTestContext: This is our Virtual DBContext. Real DBContext is “TestDBContext”. If I want to make some fake update, I avoid making changes on root DBContext. I prefer to update the inherited VirtualDBContext.
- “public class VirtualTestContext : TestDBContext”: We inherited VirtualTestContext from TestDBContext.
- “public DbSet<CustomUserVirtualDBModel> CustomUserVirtualDBModel { get; set; }”: We will declare “CustomUserVirtualDBModel” like a table on the Database as DbSet.
- “protected override void OnModelCreating()”: We have to make some declarations about our CustomUserVirtualDBModel, while is model creating on runtime.
- * “entity.HasNoKey()”: We have to call HasNoKey() method for our virtual Entity. Because Entity always needs “Primary Key”, unless we specify otherwise.
namespace DAL.PartialEntities
{
public class VirtualTestContext : TestDBContext
{
public VirtualTestContext() { }
public VirtualTestContext(DbContextOptions <TestDBContext> options): base(options){} public DbSet<CustomUserVirtualDBModel> CustomUserVirtualDBModel { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<CustomUserVirtualDBModel>(entity =>
{
entity.HasNoKey();
});
}
}
}
Now we can call our RawSql with the “FromSqlRaw()” method. Our fake “CustomUserVirtualDBModel” is ready.
Don’t forget, actually there is no table as “CustomUserVirtualDBModel” name on SqlDB.
using (TestDBContext context = new())
{
string rawSql = "SELECT U.Name,U.Surname, ua.Address FROM Users AS u " + "INNER JOIN[dbo].[UserAddres] AS ua ON U.ID = ua.UserId " + "WHERE u.BirthDate IS NOT NULL"; DbSet<CustomUserVirtualDBModel> entites = context.Set<CustomUserVirtualDBModel>(); List<CustomUserVirtualDBModel> data = entites.FromSqlRaw(rawSql).ToList(); return data;
}
“Life is a matter of choices, and every choice you make makes you.” — John C. Maxwell
3.Solution: If you don’t want to create View on SqlDB or not to add a fake DBSet to DBContext you could create your own tool for rawSQL.
DbContextExtensions.cs: This is DBContext extension class DbContextExtensions. We have two custom ExtensionQuery() methods. Synchron and Asynchronous methods.
- We will create “Command” from the current context. Database and set the “rawSql” as a value to the “CommandText” property. “DBCommand” is oldies but goldies class for all time :)
- If you execute a procedure and have any parameters, you can add Parameters to the DBCommand.
- We will get DbDataReader with “command.ExecuteReader()” and send it to the ReadData() method as a parameter. We will talk about ReadData() method later. In the end, we will close to connection and return the “IEnumerable<Dictionary<string, object>>” with dataRow.
- We will get all result data from the reader and Return the List of Dictionary. Firstly we will create “List<Dictionary<string, object>>”. And we will loop into the reader object.
- We will create ExpandoObject(). We will get the property of every item by using reflection. We will set the name as a key and value to the dictionary and we will add it to the expando object.
- We will add every row to the dataList. expando object’s every List item represents in one row and every dictionary item of the list is represented in one column of the result of the raw SQL. And finally we will return to List of Dictionary dataList.
DBContextExtensions.cs:
“It’s not hard to make decisions when you know what your values are. ” — Roy Disney
Conveter.cs:
It is time to convert result “IEnumerable<Dictionary<string,object>>” to “List<T>” with custom map schema like AutoMapper.
- “Converter” class is Generic IDisposable class. We can use it with “using(){}” for calling GC at the end.
- “ColumnMatchTable” is used for matching columns between DBModel to ViewModel.
- DictionaryToList() is our main method. We will get a dictionary as a parameter. “list” item is our return object. We will loop in the dictionary and get every row item. Every Row is a model. And every model has lots of properties. We will get all of them and put them into the “List<T>”
- We will create an instance of the <T> model. And we will loop into the model Properties. We will get every property and set the value to the instance model.
- The dictionary key is our column name and the value is our column value. We will get “PropertyInfo” from the column name with the reflection. We will talk about “CheckedMapedKey()” in a next section. We will check the “Nullable.GetUnderlyingType(t) ?? t” nullable column type and we will check “System.DBNull” and get the value of this property. And finally, we will set the model of the value and we will add to the List.
- CheckedMapedKey(): This is our ViewModel and DBModel columns Match template. We will put all colum match keywords into the Dictionary and set them to the ColumnMatchTable Dictionary property of the Converter class. It works like “AutoMapper” somehow :)
Converter.cs: Finally we got our rawSql result as a Dictionary and now we will convert it to the List<T> and return the result. First foreach-loop gets Rows of raw SQL Query results and second-foreach Loop gets columns of every row.
Converter.cs:
How To Use:
program.cs: We will prepare our raw SQL query. Later, we will get the result with the below “ExecuteQuery()” method. And most important thing is, we don’t have to declare any model any more. (DbSet<T>.SqlQuery())
“context.ExecuteQuery(rawSql)”
We will declare “Converter<CustomUserViewModel>” convert class for converting “List<Dictionary<>>” to “List<T>”. And later we will set the “ColumnMatchTable” property for mapping columns between ViewModel and DBModel. And finally, we will return List<T> data model.
program.cs:
using (DAL.PartialEntities.VirtualTestContext context = new())
{
try
{
string rawSql = "SELECT Name as Ad, Surname as Soyad, TC, " +
"Password as Sifre FROM [dbo].[Users]";
rawSql += txtUserID.Text.Trim() != "" ? $" WHERE Id = {txtUserID.Text} AND " + $"BirthDate is not null" : " WHERE BirthDate is not null"; var resultList = context.ExecuteQuery(rawSql);
using (Converter<CustomUserViewModel> convert = new())
{
convert.ColumnMatchTable = new Dictionary<string, string>(
{
{"TC", "Tc" }
};
var data = convert.DictionaryToList(resultList);
return data;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
“No one saves us but ourselves. No one can and no one may. We ourselves must walk the path.” — Buddha
I know DbDataReader, DbCommand classes are historical. But they are very performance tools. And they could be life saver in urgent times.
How To Write Linq Query With String TableName :
In this case “DB_USER” table will create again for every year. And the old one’s name, change with the year tag like this “DB_USER2019" every new year.
What about if we want to get a report with passed years’ DB_USER table records? We have to get the “DB_USER” table name as a string parameter. Of course below code is not working. Because string “tableName” is meaningless in this query.
public ServiceResponse<CustomUserModel> GetAllUsersByTable(string tableName)
{
var response = new ServiceResponse<CustomUserModel>(null);
try
{
var users = from u in "tableName"
.Where(u=>u.Deleted!=true)
.Select(cus => new CustomUserModel
{
Name = cus.Name,
LastName = cus.LastName,
UserName = cus.UserName,
Password = cus.Password,
Email = cus.Email,
Gsm = cus.Gsm,
IdUser = cus.IdUser,
}).ToList();
}
}We have to convert from this string table name to Entity. We have a third-party library for this. Thanks my god, “Linq2DB.EntityFrameworkCore” is a life-saver library at that point :)
var users = _context.Set<DbUser()
.ToLinqToDBTable().TableName(tableName)
.Where(u=>u.Deleted!=true)
.Select(cus => new CustomUserModel
{
Name = cus.Name,
LastName = cus.LastName,
UserName = cus.UserName,
Password = cus.Password,
Email = cus.Email,
Gsm = cus.Gsm,
IsAdmin = cus.IsAdmin,
IdUser = cus.IdUser,
CreDate = cus.CreDate
}).ToList();
The below method converts the string parameter to Entity. And of course, you have to have these above backup Entities in the DBContext. And all BackUp Entites must have to same properties.
.ToLinqToDBTable().TableName(tableName)
Conclusion:
In this article, we tried to fix some deficiencies in the Entity. I think (EF) Core Product Group tries to avoid using RawSql as possible. And I think they have very good reasons to do so. But sometimes, we developers don’t have any chance to write without raw SQL. Like Hanna SAP integration or executing some external Procedure.
So at that point, we could have to walk in our own path by using the custom tools like the “DBContextExtensions, Converter” class. Or we can create View on SqlDB for every ViewModel or we can create fake DBSet and add to the DBContext.
In the final case, we try to figure out using string variables in Linq Query and get result without writing raw SQL. I was suffering, when I had to get a report from backup tables until linq2db comes to my help.
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 :
- https://learn.microsoft.com/en-us/ef/core/modeling/keyless-entity-types?tabs=data-annotations
- https://www.pluralsight.com/courses/entity-framework-core-2-getting-started
- https://www.thereformedprogrammer.net/ef-core-in-depth-soft-deleting-data-with-global-query-filters/
- https://stackoverflow.com/questions/26749429/anonymous-type-result-from-sql-query-execution-entity-framework
