در
مطلب قبلی، مدل EAV را معرفی کردیم و گفتیم که این نوع پیاده‌سازی در واقع یک SQL Smell است؛ زیرا کوئری نویسی را سخت میکند و همچنین به دلیل عدم امکان تعریف constraints، کنترلی بر روی صحت دیتاهای وارده شد نخواهیم داشت. در نهایت با برنامه‌ای روبرو خواهیم شد که درک صحیحی از ماهیت دیتا ندارد. اما اگر در شرایطی مجبور به استفاده‌ی از این مدل هستید، بهتر است از فرمت JSON برای ذخیره‌سازی دیتای داینامیک استفاده کنید. بیشتر دیتابیس‌های رابطه‌ایی به صورت native از نوع داده‌ایی JSON پشتیبانی میکنند:
CREATE TABLE EmployeeJsonAttributes (
Id int NOT NULL AUTO_INCREMENT,
EmployeeId int NOT NULL,
Attributes json DEFAULT NULL,
PRIMARY KEY (Id),
FOREIGN KEY (EmployeeId) REFERENCES EmployeeEav (Id) ON DELETE CASCADE
)
همانطور که مشاهده میکنید در اینجا تایپ ستون Attributes، به JSON تنظیم شده است. بنابراین می‌توانیم از قابلیت‌های توکار دیتابیس (MySQL در مطلب جاری) برای ذخیره و بازیابی داده‌های JSON استفاده کنیم. در ادامه دو روش ذخیره JSON را مشاهده میکنید:
INSERT INTO EmployeeJsonAttributes VALUES (
101,
'{
"name": "Jon",
"lastName": "Doe",
"dateOfBirth": "1989-01-01 10:10:10+05:30",
"skills": [ "C#", "JS" ],
"address": {
"country": "UK",
"city": "London",
"email": "jon.doe@example.com"
}
}'
)
INSERT INTO efcoresample.EmployeeJsonAttributes VALUES (
101,
JSON_OBJECT(
"name", "Jon",
"lastName", "Doe",
"dateOfBirth", "1989-01-01 10:10:10+05:30",
"skills", JSON_ARRAY("C#", "JS"),
"address", JSON_OBJECT(
"country", "UK",
"city", "London",
"email", "jon.doe@example.com"
)
)
) به عنوان مثال در ادامه میخواهیم کشور محل تولد یک کاربر خاص را نمایش دهیم. برای اینکار می‌توانیم از JSON_EXTRACT استفاده کنیم:
SELECT JSON_EXTRACT(Attributes, '$.address.country') as Country
FROM EmployeeJsonAttributes
WHERE EmployeeId = 101;
-- Conutry
-- "UK"
همچنین می‌توانیم از عملگر column-path نیز به جای JSON_EXTRACT استفاده کنیم:
SELECT Attributes -> '$.address.country' as Country
FROM EmployeeJsonAttributes
WHERE EmployeeId = 101;
-- Conutry
-- "UK"
بنابراین به راحتی می‌توانیم کوئری مطلب قبل را اینگونه بازنویسی کنیم:
SELECT EmployeeId, Attributes ->> '$.DateOfBirth' AS BirthDate FROM EmployeeJsonAttributes
WHERE Attributes ->> '$.DateOfBirth' > DATE_SUB(CURRENT_DATE(), INTERVAL 25 YEAR)
همانطور که مشاهده میکنید در کوئری فوق یک عملگر < دیگر نیز اضافه کرده‌ایم. هدف از آن حذف “” از خروجی نهایی می‌باشد.
استفاده از JSON در EF Core
متاسفانه در EF Core به صورت مستقیم نمی‌توانیم از JSON درون کلاس‌های سی‌شارپ استفاده کنیم (
+ )، در نتیجه در سمت کلاس‌های سی‌شارپ باید از string استفاده کنیم و به نوعی به EF Core اطلاع دهیم که تایپ ستون موردنظرمان JSON است. در نتیجه خروجی نهایی درون دیتابیس، یک فیلد با تایپ JSON خواهد بود. برای اینکار به دو شیوه می‌توانیم تایپ ستون موردنظر را تعیین کنیم:
// Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Employee>(entity =>
{
entity.Property(e => e.Attributes).HasColumnType("json");
});
}
// Data Annotations
[Column(TypeName = "json")]
public string Attributes { get; set; } در نهایت برای تشکیل بانک اطلاعاتی، به مدلی با ساختار زیر نیاز خواهیم داشت:
public class EmployeeJsonAttribute
{
public int Id { get; set; }
public virtual EmployeeEav Employee { get; set; }
public int EmployeeId { get; set; }
[Column(TypeName = "json")]
public string Attributes { get; set; }
}
در اینجا به جای تعریف ستون‌ها و مقادیر داینامیک‌شان از یک فیلد از نوع رشته‌ایی با نام Attributes استفاده شده است. از آنجائیکه نوع ستون در سمت دیتابیس به JSON تنظیم خواهد شد، در نتیجه هر نوع ساختار JSON معتبری را می‌توانیم درون آن ذخیره کنیم:
dbContext.EmployeeJsonAttributes.Add(new EmployeeJsonAttribute
{
EmployeeId = 101,
Attributes = JsonSerializer.Serialize(new
{
FirstName = "Sirwan",
LastName = "Afifi",
DateOfBirth = DateTime.Now.AddYears(-31)
})
});
dbContext.SaveChanges();
همانطور که اشاره شده به دلیل عدم پشتیبانی از JSON در حال حاضر در EF Core امکان کوئری نویسی بر روی ستون JSON را نداریم. در همین حد که براساس فیلدهای دیگر جستجو را انجام داده و خروجی را Deserialize کنیم:
var employee = dbContext.EmployeeJsonAttributes.Find(201);
Console.WriteLine(JsonSerializer.Deserialize<Employee>(employee.Attributes).DateOfBirth);
برای نوشتن کوئری روی ستون JSON می‌توانید از
Query Types نیز استفاده کنید.
