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 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 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 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 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);
}
}
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]);
}
}
// 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 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 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 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 QueryModel(AdventureWorksCodeFirst context)
{
TempAddressQuery = context.Addresses.Select(a => new AddressTempTableDto {
Id = a.AddressID, Name = a.AddressLine1
});
TempPartTypeQuery = context.Addresses.Select(a => new PartTypeTempTableDto {
PartTypeId = a.AddressID, Name = a.AddressLine1
});
TempManufacturerQuery = context.Addresses
.Join(context.TempAddresses,
(a) => a.AddressID,
(ta) => ta.Id,
(a, ta) => new ManufacturerTempTableDto {
Id = a.AddressID, AddressId = ta.Id, Name = a.AddressLine1
});
TempPartQuery = context.Addresses
.Join(context.TempManufacturers,
(p) => p.AddressID,
(tm) => tm.AddressId,
(p, tm) => new
{
AddressId = p.AddressID,
Id = tm.Id,
Name = tm.Name,
})
.Join(context.TempPartTypes,
(a) => a.Id,
(tmp) => tmp.PartTypeId,
(a, tmp) => new PartTempTableDto
{
PartId = a.AddressId,
PartTypeId = tmp.PartTypeId,
Name = tmp.Name,
ManufacturerId = a.Id
});
TempChairQuery = context.Addresses
.Join(context.TempParts,
(a) => a.AddressID,
(tp) => tp.PartId,
(a, tp) => new ChairTempTableDto
{
ChairId = a.AddressID,
PartId = tp.PartId,
Name = a.AddressLine1
});
TempRoomQuery = context.Addresses
.Join(context.TempChairs,
(a) => a.AddressID,
(tc) => tc.ChairId,
(a, tc) => new RoomTempTableDto
{
RoomId = a.AddressID,
Name = a.AddressLine1,
ChairId = tc.ChairId
});
TempOfficeTypeQuery = context.Addresses.Select(tot => new OfficeTypeTempTableDto {
Id = tot.AddressID, Name = tot.AddressLine1
});
TempOfficeQuery = context.Addresses
.Join(context.TempRooms,
(a) => a.AddressID,
(to) => to.RoomId,
(a, to) => new OfficeTempTableDto
{
Id = a.AddressID,
RoomId = to.RoomId,
Name = a.AddressLine1,
OfficeTypeId = context.TempOfficeTypes.FirstOrDefault().Id,
DepartmentId = context.TempDepartments.FirstOrDefault().DepartmentId
});
TempPersonQuery = context.Addresses
.Join(context.TempAddresses,
(a) => a.AddressID,
(ta) => ta.Id,
(a, ta) => new PersonTempTableDto
{
PersonId = a.AddressID,
Name = a.AddressLine2,
AddressId = ta.Id
});
TempDepartmentQuery = context.Addresses
.Join(context.TempPersons,
(a) => a.AddressID,
(tp) => tp.AddressId,
(a, tp) => new DepartmentTempTableDto
{
DepartmentId = a.AddressID,
Name = a.AddressLine1,
LeadId = tp.PersonId
});
}
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);
}
}
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);
}
}
