static void PopuplateContext(CaseStudyContext context) { context.CaseStudy.Add(new CaseStudy { CaseStudyName = "Case Study" }); context.SaveChanges(); var caseStudy = context.CaseStudy.First(); context.CommonData.Add(new CommonData { CaseStudy = caseStudy, CommonText = "First Common Data" }); context.CommonData.Add(new CommonData { CaseStudy = caseStudy, CommonText = "Second Common Data" }); context.SaveChanges(); var commonData1 = context.CommonData.First(x => x.CommonText == "First Common Data"); var commonData2 = context.CommonData.First(x => x.CommonText == "Second Common Data"); context.V1Extended.Add(new V1Extended { CommonData = commonData1, ExtendedData = "First Extended Data" }); context.V1Extended.Add(new V1Extended { CommonData = commonData2, ExtendedData = "Second Extended Data" }); context.SaveChanges(); }
static CaseStudyContext GetLocalDbDataContext() { var context = new CaseStudyContext(new DbContextOptionsBuilder <CaseStudyContext>() .UseSqlServer(LocalDb.ConnectionString) .Options); return(context); }
static IEnumerable <V1Extended> QueryFromSyntax(CaseStudyContext context, int caseStudyId) { var extRecords = from n in context.CommonData join v in context.V1Extended on n.Id equals v.CommonDataId select v; return(extRecords); }
static IEnumerable <V1Extended> QuerySQL(CaseStudyContext context, int caseStudyId) { var Sql = $@"SELECT V.* FROM V1Extended AS V JOIN CommonData AS C ON C.Id = V.CommonDataId WHERE C.CaseStudyId = {caseStudyId} ORDER BY V.CommonDataId DESC;"; var extnRecords = context.V1Extended.FromSql(Sql); return(extnRecords); }
private static void CreateDatabase(CaseStudyContext caseStudyContext) { // default language British ExecuteSqlCommand(Master, $@"CREATE DATABASE [{dbFilename}] ON ( NAME = '{dbFilename}', FILENAME = '{Filename}' )"); var context = caseStudyContext; context.Database.Migrate(); }
static IEnumerable <V1Extended> QueryLambda(CaseStudyContext context, int caseStudyId) { var extRecords = context.CommonData .Join(context.V1Extended, n => n.Id, v => v.CommonDataId, (n, v) => new { n, v }) .Where(x => x.n.CaseStudy.Id == caseStudyId) .Select(y => y.v).ToList(); return(extRecords); }
static void AddRecordsBySql(CaseStudyContext context, int caseStudyId) { var sql = $@"INSERT INTO CommonData (CaseStudyId, CommonText) SELECT {caseStudyId}, 'THIRD BY SQL' UNION ALL SELECT {caseStudyId}, 'FOURTH BY SQL';"; var param = new Object[0]; context.Database.ExecuteSqlCommand(sql, param); // get the new Ids var commonData3Id = context.CommonData.First(x => x.CommonText == "THIRD BY SQL").Id; var commonData4Id = context.CommonData.First(y => y.CommonText == "FOURTH BY SQL").Id; var sql2 = $@"INSERT INTO V1Extended (CommonDataId, ExtendedData) SELECT {commonData3Id}, 'THIRD EXTENDED' UNION ALL SELECT {commonData4Id}, 'FOURTH EXTENDED';"; context.Database.ExecuteSqlCommand(sql2, param); }