Пример #1
0
        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();
        }
Пример #2
0
        static CaseStudyContext GetLocalDbDataContext()
        {
            var context = new CaseStudyContext(new DbContextOptionsBuilder <CaseStudyContext>()
                                               .UseSqlServer(LocalDb.ConnectionString)
                                               .Options);

            return(context);
        }
Пример #3
0
        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);
        }
Пример #4
0
        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);
        }
Пример #5
0
        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();
        }
Пример #6
0
        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);
        }
Пример #7
0
        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);
        }