public void TempOnTempDependenciesInQueryOrderChanged() { var context = new AdventureWorksCodeFirst(); var queryModel = new QueryModel(context); //Attach temp tables context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempOfficeTypeQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempAddressQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempPersonQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempDepartmentQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempManufacturerQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempPartTypeQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempPartQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempChairQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempRoomQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempOfficeQuery); var list = context.TempAddresses.Select(a => new { id1 = context.TempOffices.FirstOrDefault().Id, id2 = context.TempAddresses.FirstOrDefault().Id, id3 = context.TempManufacturers.FirstOrDefault().Id, id4 = context.TempPartTypes.FirstOrDefault().PartTypeId, id5 = context.TempChairs.FirstOrDefault().ChairId, id6 = context.TempRooms.FirstOrDefault().RoomId, id7 = context.TempPersons.FirstOrDefault().PersonId, id8 = context.TempDepartments.FirstOrDefault().DepartmentId, id9 = context.TempOffices.FirstOrDefault().Id, }).ToList(); context.Dispose(); Assert.NotEmpty(list); }
public void ReuseSameAlreadyAttachedQueryOnSubsequentCall() { using (var context = new AdventureWorksCodeFirst()) { var tempAddressQuery = context.Addresses.Select(a => new AddressTempTableDto { Id = a.AddressID, Name = a.AddressLine1 }); var addressList = context .WithTempTableExpression <AdventureWorksCodeFirst>(tempAddressQuery) .TempAddresses.Join(context.Addresses, (a) => a.Id, (aa) => aa.AddressID, (at, a) => new { Id = at.Id }).ToList(); Assert.NotEmpty(addressList); var shipToAddress = context .TempAddresses.Join(context.SalesOrderHeaders, //WithTempTableExpression() not needed; temp table is already created. (a) => a.Id, (soh) => soh.ShipToAddressID, (soh, a) => new { Id = soh.Id }).ToList(); Assert.NotEmpty(shipToAddress); } }
public void LoadFromMemoryAndDatabase() { using (var context = new AdventureWorksCodeFirst()) { var maxId = context.Addresses.Max(x => x.AddressID); var totalAddressesInDb = context.Addresses.Count(); var addressesInMemory = _addressList.Select((x, i) => new AddressTempTableTwoDataSourcesDto { Id = maxId + i + 1, Name = x }); var tempAddressQuery = context.Addresses.Select(a => new AddressTempTableTwoDataSourcesDto { Id = a.AddressID, Name = a.AddressLine1 }); var totalCount = context .WithTempTableExpression <AdventureWorksCodeFirst>(addressesInMemory) .WithTempTableExpression <AdventureWorksCodeFirst>(tempAddressQuery) .TempAddressesTwoDataSources.Count(); Assert.Equal(addressesInMemory.Count() + totalAddressesInDb, totalCount); } }
public void Load1000RecordsFromMemoryAndEntireAddressTableFromDb() { var sampleList = new List <AllDataTypesDto>(); for (var i = 0; i < 1001; i++) { sampleList.Add(new AllDataTypesDto { Bigint = Int64.MaxValue, Binary = new byte[] { 0x45, 0x46 }, Bit = true, Date = DateTime.MaxValue.Date, Datetime = DateTime.MaxValue.AddMilliseconds(-2), //Time range: 00:00:00 through 23:59:59.997 https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15 Datetime2 = DateTime.MaxValue, Datetimeoffset = DateTimeOffset.UtcNow.Date, Decimal = Decimal.MaxValue, Varbinary_Max = new byte[] { 0x4B, 0x49, 0x54, 0x41 }, Float = double.MaxValue, Image = new byte[] { 0x45, 0x46 }, Int = int.MaxValue, Nchar = "Ef6TempTableKit", Ntext = "Ef6TempTableKit", Numeric = Decimal.MaxValue, Nvarchar = "Ef6TempTableKit", Real = Single.MaxValue, Smalldatetime = new DateTime(2079, 6, 5, 23, 59, 0), Smallint = Int16.MaxValue, Smallmoney = SMALL_MONEY_MAX, Text = "Ef6TempTableKit", Time = new TimeSpan(0, 4, 54, 56, 234), Tinyint = byte.MaxValue, Uniqueidentifier = Guid.NewGuid(), Varbinary = new byte[] { 0x4B, 0x49, 0x54, 0x41 }, Varchar_50 = "wqS5LQa67cxMReRRFHC5CKptEnCVqieB04mOXbBl5ahk0M3S8j" }); } using (var context = new AdventureWorksCodeFirst()) { var addressCount = context.Addresses.Count(); var addressQuery = context.Addresses.Select(x => new AllDataTypesDto { Int = x.AddressID, Varchar_50 = x.AddressLine1 }); var allDataFromDb = context .WithTempTableExpression <AdventureWorksCodeFirst>(sampleList) .WithTempTableExpression <AdventureWorksCodeFirst>(addressQuery) .AllDataTypesTempTable.ToList(); Assert.Equal(allDataFromDb.Count, sampleList.Count + addressCount); }; }
public void LoadFromMemoryAndDatabaseAppendData() { using (var context = new AdventureWorksCodeFirst()) { var maxId = context.Addresses.Max(x => x.AddressID); var totalAddressesInDb = context.Addresses.Count(); var addressesInMemory = _addressList.Select((x, i) => new AddressTempTableTwoDataSourcesDto { Id = maxId + i + 1, Name = x }); var tempAddressQuery = context.Addresses.Select(a => new AddressTempTableTwoDataSourcesDto { Id = a.AddressID, Name = a.AddressLine1 }); var totalCount = context .WithTempTableExpression <AdventureWorksCodeFirst>(addressesInMemory) .WithTempTableExpression <AdventureWorksCodeFirst>(tempAddressQuery) .TempAddressesTwoDataSources.Count(); Assert.Equal(addressesInMemory.Count() + totalAddressesInDb, totalCount); var updatedAddressList = new List <string> { "8157 W. Book", "6696 Anchor Drive", "6872 Thornwood Dr.", "636 Vine Hill Way", "7484 Roundtree Drive" }; var totalCountQuery = context.WithTempTableExpression <AdventureWorksCodeFirst>(updatedAddressList.Select((x, i) => new AddressTempTableTwoDataSourcesDto { Id = maxId + i + 1 + addressesInMemory.Count(), Name = x })); totalCount = totalCountQuery.TempAddressesTwoDataSources.Count(); Assert.Equal(addressesInMemory.Count() + totalAddressesInDb + updatedAddressList.Count(), totalCount); } }
// O f f i c e // / \ \ // / \ \ // / \ \ // Room Department OfficeType // / | // Chair People // / | // Part Address // / \ // Manufacturer PartType // / // Address /// /// Queries and how are they joined doesn't matter! /// This is only for internal test purpose. Tables such as Address, PartType and OfficeType don't have any dependencies whereas Office depends on all nodes below it (subtree). private AdventureWorksCodeFirst CreateTempQueries() { var context = new AdventureWorksCodeFirst(); var queryModel = new QueryModel(context); //Attach temp tables context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempOfficeTypeQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempAddressQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempManufacturerQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempPartTypeQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempPartQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempChairQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempRoomQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempPersonQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempDepartmentQuery); context.WithTempTableExpression <AdventureWorksCodeFirst>(queryModel.TempOfficeQuery); return(context); }
public void GetAddress() { using (var context = new AdventureWorksCodeFirst()) { var tempAddressQuery = context.Addresses.Select(a => new AddressTempTableDto { Id = a.AddressID, Name = a.AddressLine1 }); var addressList = context .WithTempTableExpression <AdventureWorksCodeFirst>(tempAddressQuery) .TempAddresses.Join(context.Addresses, (a) => a.Id, (aa) => aa.AddressID, (at, a) => new { Id = at.Id }).ToList(); Assert.NotEmpty(addressList); } }
public void GetCustomerDataUsingLinqKitUseCriteria() { Expression <Func <SalesOrderHeader, bool> > criteria1 = p => p.TotalDue > 23153; using (var context = new AdventureWorksCodeFirst()) { var tempSalesOrderQuery = context.Customers.AsExpandable().Select(a => new CustomerTempTableDto { Id = a.CustomerID }); var query = from c in context.WithTempTableExpression <AdventureWorksCodeFirst>(tempSalesOrderQuery).TempCustomers.AsExpandable() join h in context.Customers on c.Id equals h.CustomerID where h.SalesOrderHeaders.Any(criteria1.Compile()) select h.Person; Assert.NotEmpty(query.ToList()); } }
public void ReinitializeTempTableContainer() { var wantedResult = new Dictionary <int, string>() { { 1, "1970 Napa Ct." }, { 2, "9833 Mt. Dias Blv." }, { 3, "7484 Roundtree Drive" }, { 4, "9539 Glenside Dr" }, { 5, "1226 Shoe St." }, { 6, "1399 Firestone Drive" } }; var result = new Dictionary <int, string>(); using (var context = new AdventureWorksCodeFirst()) { for (var i = 0; i < 6; i++) { var tempAddressQuery = context.Addresses.Select(a => new AddressTempTableDto { Id = a.AddressID, Name = a.AddressLine1 }).OrderBy(ta => ta.Id).Skip(i).Take(1); context.ReinitializeTempTableContainer(); var address = context .WithTempTableExpression <AdventureWorksCodeFirst>(tempAddressQuery) .TempAddresses.Join(context.Addresses, (a) => a.Id, (aa) => aa.AddressID, (at, a) => new { Id = at.Id, AddressLine1 = a.AddressLine1 }).Single(); result.Add(address.Id, address.AddressLine1); } } for (var i = 0; i < 6; i++) { Assert.Equal(wantedResult[i + 1], result[i + 1]); } }
public void ThrowExceptionWrongFormmaterSignatureIsSpecified() { Assert.Throws <EF6TempTableKitGenericException>(() => { using (var context = new AdventureWorksCodeFirst()) { var addressList = _addressList.Select(x => new AddressTempTableWrongAttribute1Dto { Name = x }); var data = context .WithTempTableExpression <AdventureWorksCodeFirst>(addressList) .TempAddresses.Join(context.Addresses, (a) => a.Id, (aa) => aa.AddressID, (at, a) => new { Id = at.Id }).ToList(); } }); }
public void WhereClauseWithMoreThan10Parameters_ConditionIsNeverMet_CompiledAndExecutedSuccesfully() { var p0 = "test"; var p1 = "test"; var p2 = "test"; var p3 = "test"; var p4 = "test"; var p5 = "test"; var p6 = "test"; var p7 = "test"; var p8 = "test"; var p9 = "test"; var p10 = "test"; var falseParam = false; using (var context = new AdventureWorksCodeFirst()) { var departmentQuery = context.Departments .Where(x => x.Name == p0 && x.Name == p1 || x.Name == p2 || x.Name == p3 && x.Name == p4 || x.Name == p5 && x.Name == p6 || x.Name == p7 || x.Name == p8 && x.Name == p9 || x.Name == p10 || true == falseParam ).Select(x => new DepartmentTempTableDto() { Name = x.Name }); var temp = context.WithTempTableExpression <AdventureWorksCodeFirst>(departmentQuery).TempDepartments; var result = temp.ToList(); Assert.True(!result.Any()); } }
public void GetAddressMultipleId() { Assert.Throws <System.Data.Entity.Core.EntityCommandExecutionException>(() => { using (var context = new AdventureWorksCodeFirst()) { var tempAddressQuery = context.Addresses.Select(a => new AddressTempTableMultipleIdDto { //AddressID is mapped twice; EF throws exception Id = a.AddressID, Id2 = a.AddressID, Name = a.AddressLine1 }); var addressList = context .WithTempTableExpression <AdventureWorksCodeFirst>(tempAddressQuery) .TempAddressesMultipleId.Join(context.Addresses, (a) => a.Id, (aa) => aa.AddressID, (at, a) => new { Id = at.Id }).ToList(); } }); }
public void GetProductDataUsingLinqKitUseCriteriaAndInvoke() { Expression <Func <Product, bool> > criteria1 = p => p.SafetyStockLevel > 100; Expression <Func <Product, bool> > criteria2 = p => criteria1.Invoke(p) || p.ProductNumber.Contains("a"); using (var context = new AdventureWorksCodeFirst()) { var tempProductQuery = context.Products.AsExpandable().Select(a => new ProductTempTableDto { Id = a.ProductID, Name = a.Name }); var productList = context .WithTempTableExpression <AdventureWorksCodeFirst>(tempProductQuery) .TempProducts.Join(context.Products, (a) => a.Id, (aa) => aa.ProductID, (at, a) => new { Id = at.Id }) .AsExpandable() .ToList(); Assert.NotEmpty(productList); } }
public void ProductListWithCategoryDetailsTwoTempTables() { using (var context = new AdventureWorksCodeFirst()) { var categories = context.ProductCategories.Select(pc => new ProductCategoryTempTableDto { Id = pc.ProductCategoryID, CategoryName = pc.Name }); var subCategories = context.ProductSubcategories.Select(psc => new ProductSubCategoryTempTableDto { Id = psc.ProductSubcategoryID, CategoryId = psc.ProductCategoryID, CategoryName = psc.Name, }); var productsQuery = context .WithTempTableExpression <AdventureWorksCodeFirst>(categories) .WithTempTableExpression <AdventureWorksCodeFirst>(subCategories) .WorkOrders .Join(context.Products, (wo) => wo.ProductID, (p) => p.ProductID, (wo, p) => new { WorkOrderId = wo.WorkOrderID, ScrappedQty = wo.ScrappedQty, ProductId = p.ProductID, ProductSubcategoryId = p.ProductSubcategoryID, ProductNumber = p.ProductNumber, }) .Join(context.TempProductCategories, (wo) => wo.ProductSubcategoryId, (ps) => ps.Id, (wo, ps) => new { WorkOrderId = wo.WorkOrderId, ScrappedQty = wo.ScrappedQty, ProductId = wo.ProductId, ProductSubcategoryId = wo.ProductSubcategoryId, ProductNumber = wo.ProductNumber, CategoryId = ps.Id }) .Join(context.TempProductSubCategories, (wo) => wo.CategoryId, (temp) => temp.CategoryId, (wo, temp) => new { WorkOrderId = wo.WorkOrderId, ScrappedQty = wo.ScrappedQty, ProductId = wo.ProductId, ProductSubcategoryId = wo.ProductSubcategoryId, ProductName = wo.ProductNumber, CategoryName = temp.CategoryName, }); var productList = productsQuery.ToList(); Assert.NotEmpty(productList); } }
public void ProductListWithCategoryDetails() { using (var context = new AdventureWorksCodeFirst()) { var productsCountCategoryQuery = context.Products.Join(context.ProductSubcategories, (p) => p.ProductSubcategoryID, (pcs) => pcs.ProductSubcategoryID, (p, pcs) => new { CategoryId = pcs.ProductCategoryID, ProductId = p.ProductID }) .GroupBy((cp) => cp.CategoryId, (x) => new { x.CategoryId, x.ProductId }) .Select(x => new { CategoryId = x.Key, ProductCount = x.Count() }) .Join(context.ProductCategories, (tr) => tr.CategoryId, (p) => p.ProductCategoryID, (tr, p) => new ProductCategoryCountTempTableDto { CategoryId = tr.CategoryId, CategoryName = p.Name, ProductCount = tr.ProductCount } ); var productsQuery = context .WithTempTableExpression <AdventureWorksCodeFirst>(productsCountCategoryQuery) .WorkOrders .Join(context.Products, (wo) => wo.ProductID, (p) => p.ProductID, (wo, p) => new { WorkOrderId = wo.WorkOrderID, ScrappedQty = wo.ScrappedQty, ProductId = p.ProductID, ProductSubcategoryId = p.ProductSubcategoryID, ProductNumber = p.ProductNumber, }) .Join(context.ProductSubcategories, (wo) => wo.ProductSubcategoryId, (ps) => ps.ProductSubcategoryID, (wo, ps) => new { WorkOrderId = wo.WorkOrderId, ScrappedQty = wo.ScrappedQty, ProductId = wo.ProductId, ProductSubcategoryId = wo.ProductSubcategoryId, ProductNumber = wo.ProductNumber, CategoryId = ps.ProductCategoryID }) .Join(context.TempProductCategoryCounts, (wo) => wo.CategoryId, (temp) => temp.CategoryId, (wo, temp) => new { WorkOrderId = wo.WorkOrderId, ScrappedQty = wo.ScrappedQty, ProductId = wo.ProductId, ProductSubcategoryId = wo.ProductSubcategoryId, ProductName = wo.ProductNumber, CategoryName = temp.CategoryName, ProductCountPerCategory = temp.ProductCount }); var productList = productsQuery.ToList(); Assert.NotEmpty(productList); var productCount = productsQuery.Count(); Assert.True(productCount > 0); } }
public void MapNetToSqlDataTypes() { using (var context = new AdventureWorksCodeFirst()) { var allDataTypesList = new List <AllDataTypesDto> { new AllDataTypesDto { Bigint = Int64.MaxValue, Binary = new byte[] { 0x45, 0x46 }, Bit = true, Date = DateTime.MaxValue.Date, Datetime = DateTime.MaxValue.AddMilliseconds(-2), //Time range: 00:00:00 through 23:59:59.997 https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15 Datetime2 = DateTime.MaxValue, Datetimeoffset = DateTimeOffset.UtcNow.Date, Decimal = Decimal.MaxValue, Varbinary_Max = new byte[] { 0x4B, 0x49, 0x54, 0x41 }, Float = double.MaxValue, Image = new byte[] { 0x45, 0x46 }, Int = int.MaxValue, Nchar = "Ef6TempTableKit", Ntext = "Ef6TempTableKit", Numeric = Decimal.MaxValue, Nvarchar = "Ef6TempTableKit", Real = Single.MaxValue, Smalldatetime = new DateTime(2079, 6, 5, 23, 59, 0), Smallint = Int16.MaxValue, Smallmoney = SMALL_MONEY_MAX, Text = "Ef6TempTableKit", Time = new TimeSpan(0, 4, 54, 56, 234), Tinyint = byte.MaxValue, Uniqueidentifier = Guid.NewGuid(), Varbinary = new byte[] { 0x4B, 0x49, 0x54, 0x41 }, Varchar_50 = "wqS5LQa67cxMReRRFHC5CKptEnCVqieB04mOXbBl5ahk0M3S8j" } }; var allDataTypeItemFromDb = context .WithTempTableExpression <AdventureWorksCodeFirst>(allDataTypesList) .AllDataTypesTempTable.First(); var allDataTypeItemFromMemory = allDataTypesList.First(); Assert.Equal(allDataTypeItemFromDb.Bigint, allDataTypeItemFromMemory.Bigint); Assert.Equal(allDataTypeItemFromDb.Binary, allDataTypeItemFromMemory.Binary); Assert.Equal(allDataTypeItemFromDb.Bit, allDataTypeItemFromMemory.Bit); Assert.Equal(allDataTypeItemFromDb.Date, allDataTypeItemFromMemory.Date); Assert.Equal(allDataTypeItemFromDb.Datetime.ToString("yyyy-MM-dd HH:mm:ss.fff"), allDataTypeItemFromMemory.Datetime.ToString("yyyy-MM-dd HH:mm:ss.fff")); Assert.Equal(allDataTypeItemFromDb.Datetime2.ToString("yyyy-MM-dd HH:mm:ss.fffffff"), allDataTypeItemFromMemory.Datetime2.ToString("yyyy-MM-dd HH:mm:ss.fffffff")); Assert.Equal(allDataTypeItemFromDb.Datetimeoffset.Date, allDataTypeItemFromMemory.Datetimeoffset.Date); Assert.Equal(allDataTypeItemFromDb.Decimal, allDataTypeItemFromMemory.Decimal); Assert.Equal(allDataTypeItemFromDb.Varbinary_Max, allDataTypeItemFromMemory.Varbinary_Max); Assert.Equal(allDataTypeItemFromDb.Float, allDataTypeItemFromMemory.Float); Assert.Equal(allDataTypeItemFromDb.Image, allDataTypeItemFromMemory.Image); Assert.Equal(allDataTypeItemFromDb.Int, allDataTypeItemFromMemory.Int); Assert.Equal(allDataTypeItemFromDb.Nchar, allDataTypeItemFromMemory.Nchar); Assert.Equal(allDataTypeItemFromDb.Ntext, allDataTypeItemFromMemory.Ntext); Assert.Equal(allDataTypeItemFromDb.Numeric, allDataTypeItemFromMemory.Numeric); Assert.Equal(allDataTypeItemFromDb.Nvarchar, allDataTypeItemFromMemory.Nvarchar); Assert.Equal(allDataTypeItemFromDb.Real, allDataTypeItemFromMemory.Real); Assert.Equal(allDataTypeItemFromDb.Smalldatetime.ToString("yyyy-MM-dd HH:mm:ss"), allDataTypeItemFromMemory.Smalldatetime.ToString("yyyy-MM-dd HH:mm:ss")); Assert.Equal(allDataTypeItemFromDb.Smallint, allDataTypeItemFromMemory.Smallint); Assert.Equal(allDataTypeItemFromDb.Smallmoney, allDataTypeItemFromMemory.Smallmoney); Assert.Equal(allDataTypeItemFromDb.Text, allDataTypeItemFromMemory.Text); Assert.Equal(allDataTypeItemFromDb.Time, allDataTypeItemFromMemory.Time); Assert.Equal(allDataTypeItemFromDb.Tinyint, allDataTypeItemFromMemory.Tinyint); Assert.Equal(allDataTypeItemFromDb.Uniqueidentifier, allDataTypeItemFromMemory.Uniqueidentifier); Assert.Equal(allDataTypeItemFromDb.Varbinary, allDataTypeItemFromMemory.Varbinary); Assert.Equal(allDataTypeItemFromDb.Varchar_50, allDataTypeItemFromMemory.Varchar_50); } }