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);
}