در مطلب قبلی، مدل 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 )
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; } }
dbContext.EmployeeJsonAttributes.Add(new EmployeeJsonAttribute { EmployeeId = 101, Attributes = JsonSerializer.Serialize(new { FirstName = "Sirwan", LastName = "Afifi", DateOfBirth = DateTime.Now.AddYears(-31) }) }); dbContext.SaveChanges();
var employee = dbContext.EmployeeJsonAttributes.Find(201); Console.WriteLine(JsonSerializer.Deserialize<Employee>(employee.Attributes).DateOfBirth);
برای نوشتن کوئری روی ستون JSON میتوانید از Query Types نیز استفاده کنید.