در EF Core دسترسی به جدول join وجود دارد: «شروع به کار با EF Core 1.0 - قسمت 8 - بررسی رابطهی Many-to-Many»
var usersWithoutUnits = ctx.Users.Where(x => !x.UsersJoinUnits.Any(y => y.UserId == x.UserId)).ToList();
var usersWithoutUnits = ctx.Users.Where(x => !x.UsersJoinUnits.Any(y => y.UserId == x.UserId)).ToList();
namespace RavenDBSample01.BlogModels { public class BlogConfig { public string Id { set; get; } public string Title { set; get; } public string Description { set; get; } // ... more items here } public class User { public string Id { set; get; } public string FullName { set; get; } public string Email { set; get; } // ... more items here } }
public class Post { public string Id { set; get; } public string Title { set; get; } public string Body { set; get; } public ICollection<string> Tags { set; get; } public string AuthorId { set; get; } public string PostCommentsId { set; get; } public int CommentsCount { set; get; } } public class Comment { public string Id { set; get; } public string Body { set; get; } public string AuthorName { set; get; } public DateTime CreatedAt { set; get; } } public class PostComments { public List<Comment> Comments { set; get; } public string LastCommentId { set; get; } }
;WITH cteBed ([Counter], id_doc , [Year] ,id_Total , date_duc ,Number_Temp , number_fix , sumbed , sumbes , row_no ) AS ( SELECT [Counter], d.id_doc , d.[Year] ,r.id_Total , d.date_duc ,d.Number_Temp ,d.number_fix , SUM( r.Mablagh_bed) OVER(PARTITION BY d.[Year] ,r.id_Total , d.Number_Temp) AS sumbed , sumbes= 0, ROW_NUMBER() OVER (PARTITION BY d.[Year] ,r.id_Total , d.date_duc , d.Number_Temp , d.number_fix ORDER BY d.date_duc )AS row_no FROM tbl_Records r JOIN tbl_Documents d ON d.id_doc = r.id_doc ) , cteBes ([Counter], id_doc , [Year] ,id_Total , date_duc ,Number_Temp , number_fix , sumbed , sumbes , row_no) AS ( SELECT [Counter], d.id_doc , d.[Year] ,r.id_Total , d.date_duc ,d.Number_Temp ,d.number_fix , sumbed = 0 , SUM( r.Mablagh_bes ) OVER(PARTITION BY d.[Year] ,r.id_Total , d.Number_Temp ) AS sumbes, ROW_NUMBER() OVER (PARTITION BY d.[Year] ,r.id_Total , d.date_duc ,d.Number_Temp , d.number_fix ORDER BY d.date_duc )AS row_no FROM tbl_Records r JOIN tbl_Documents d ON d.id_doc = r.id_doc ) SELECT [Counter], id_doc , [Year] ,id_Total , date_duc ,Number_Temp , number_fix , sumbed , sumbes , amountBed ,amountBes ,SUM(amountBed)OVER( ORDER BY [Year] ,id_Total , date_duc , number_Temp, number_Fix ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS bed ,SUM(amountBes)OVER( ORDER BY [Year] ,id_Total , date_duc , number_Temp, number_Fix ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS bes FROM ( SELECT [Counter], id_doc , [Year] ,id_Total , date_duc ,Number_Temp , number_fix , sumbed , sumbes , amountBed = CASE WHEN id_Total LIKE '1%' OR Id_Total LIKE '2%' OR Id_Total LIKE '7%' OR Id_Total LIKE '8%' THEN (tt.sumbed-tt.sumbes) ELSE 0 END , amountBes=CASE WHEN Id_Total LIKE '3%' OR Id_Total LIKE '4%' OR Id_Total LIKE '5%' OR Id_Total LIKE '6%' OR Id_Total LIKE '9%' THEN (tt.sumbes-tt.sumbed)ELSE 0 END , ROW_NUMBER() OVER (PARTITION BY [Year] ,id_Total , date_duc , Number_Temp , number_fix ORDER BY date_duc )AS row_no FROM ( SELECT * FROM cteBed cb WHERE cb.row_no = 1 UNION ALL SELECT * FROM cteBes cb WHERE cb.row_no = 1 ) AS tt ([Counter], id_doc , [Year] ,id_Total , date_duc ,Number_Temp , number_fix , sumbed , sumbes,row_no ) WHERE not(sumbed = 0 AND sumbes = 0) ) AS rr
در SampleProject1 مدل Product را داریم:
public partial class Product : Entity { public int Id { get; set; } public string Name { get; set; } public Nullable<byte> ProductTypeId { get; set; } }
public partial class ProductType : Entity { public byte Id { get; set; } public string Name { get; set; } }
List<Assembly> allAssemblies = new List<Assembly>(); string path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location); foreach (string dll in Directory.GetFiles(path, "*.Common.dll")) allAssemblies.Add(Assembly.LoadFile(dll)); var type = typeof(Entity); List<Type> types = allAssemblies .SelectMany(s => s.GetTypes()) .Where(p => type.IsAssignableFrom(p)).ToList(); List<string> entities = new List<string>(); foreach (var item in types) { entities.Add(item.Name); } types.Add(typeof(Entity));
public class ContextGenerator { public void Generate(List<string> entities, params Type[] types) { StringBuilder code = new StringBuilder(); code.AppendLine(@" using System.Data.Entity; using System.Data.Entity.Core.EntityClient; using SampleProject1.Common.Models; using SampleProject1.Common.Models.Mapping; using SampleProject2.Common.Models; using SampleProject2.Common.Models.Mapping; namespace DbContextGenerator { public partial class TestContext : DbContext { static TestContext() { Database.SetInitializer<TestContext>(null); } public TestContext() : base(""Data Source=.;Initial Catalog=Test;Integrated Security=True;MultipleActiveResultSets=True"") { } "); var pluralizeHelper = new PluralizeHelper(); foreach (var entity in entities) { code.AppendLine($@"public DbSet<{entity}> {pluralizeHelper.Pluralize(entity)} {{ get; set; }}"); } code.AppendLine(@"protected override void OnModelCreating(DbModelBuilder modelBuilder)"); code.AppendLine(@"{"); foreach (var entity in entities) { code.AppendLine($@"modelBuilder.Configurations.Add(new {entity}Map());"); } code.AppendLine(@"}"); code.AppendLine(@"}"); code.AppendLine(@"}"); CSharpCodeProvider provider = new CSharpCodeProvider(); CompilerParameters parameters = new CompilerParameters(); parameters.ReferencedAssemblies.Add("System.Drawing.dll"); parameters.ReferencedAssemblies.Add("System.Data.dll"); parameters.ReferencedAssemblies.Add("System.Data.Entity.dll"); parameters.ReferencedAssemblies.Add("System.ComponentModel.dll"); foreach (var type in types) { parameters.ReferencedAssemblies.Add(type.Assembly.Location); } parameters.ReferencedAssemblies.Add(typeof(DbSet).Assembly.Location); parameters.ReferencedAssemblies.Add(typeof(DbContext).Assembly.Location); parameters.ReferencedAssemblies.Add(typeof(IQueryable).Assembly.Location); parameters.ReferencedAssemblies.Add(typeof(IQueryable<>).Assembly.Location); parameters.ReferencedAssemblies.Add(typeof(System.ComponentModel.IListSource).Assembly.Location); parameters.GenerateExecutable = false; parameters.GenerateInMemory = false; parameters.OutputAssembly = "ProjectContext.dll"; CompilerResults results = provider.CompileAssemblyFromSource(parameters, code.ToString()); if (results.Errors.HasErrors) { StringBuilder sb = new StringBuilder(); foreach (CompilerError error in results.Errors) { sb.AppendLine(String.Format("Error ({0}): {1}", error.ErrorNumber, error.ErrorText)); } throw new InvalidOperationException(sb.ToString()); } } }
new ContextGenerator().Generate(entities, types.ToArray()); // generate dbContext
حال برای استفاده از Context تولید شده، به صورت زیر شیءایی را ساخته:
static DbContext _dbContext=null; public static DbContext GetDbContextInstance() { if (_dbContext == null) { string path = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location); var dllversionAssm = Assembly.LoadFile(path + "\\ProjectContext.dll"); Type type = dllversionAssm.GetType("DbContextGenerator.TestContext"); _dbContext = (DbContext)Activator.CreateInstance(type); } return _dbContext; }
و سپس برای ساخت DbSet از هر Entity به کد زیر نیاز خواهیم داشت:
public static System.Data.Entity.DbSet<T> Get<T>() where T : class { var set = GetDbContextInstance().Set<T>(); return set; }
هم اکنون میتوان رکوردهای Entityها را واکشی کرده و یا آنها را با یکدیگر Join بزنیم:
var products = Get<Product>().ToList(); var productTypes = Get<ProductType>().ToList(); var query = from p in Get<Product>() join pt in Get<ProductType>() on p.ProductTypeId equals pt.Id select new { Id = p.Id, Name = p.Name, ProductType = pt.Name }; var JoinResult = query.ToList();
و نتیجه واکشی ها