public void Extensions()
{
using (var db = new DBTestDbContext(@"Data Source=localhost;Initial Catalog=DBTest;Integrated Security=True"))
{
var sourceList = db.City.AsNoTracking().Include(c => c.Student).ToList();
var table = sourceList.ToDataTable();
var myList = table.ToEnumerable <City>();
}
}
public void DataContext()
{
using (var db = new DBTestDbContext(@"Data Source=localhost;Initial Catalog=DBTest;Integrated Security=True;"))
{
//goto NewTests;
var random = new Random();
////Bulk insert test
//db.Student.AddRange(Enumerable.Range(0, 10000).Select(_ => new Student
//{
// Name = Enumerable.Range(0, 5).Select(__ => ((char)random.Next('A', 'Z' + 1)).ToString()).Join()
//}));
//db.BulkSaveChanges();
////Bulk update test
//db.Student.Where(s => s.Id >= 6006 && s.Id <= 11006).ForEach(m =>
//{
// m.Name = Enumerable.Range(0, 5).Select(_ => ((char)random.Next('0', '9' + 1)).ToString()).Join();
// m.BirthDate = DateTime.Now.AddHours(random.Next(0, 200));
//});
//db.BulkSaveChanges();
////Bulk delete test
//db.Student.Where(s => s.Id >= 1006 && s.Id <= 6005).ToList().ForValue(list => db.Student.RemoveRange(list));
//db.BulkSaveChanges();
Debugger.Break();
//a simple pagination method
var pagedResults = db.City.AsNoTracking()
.Where(c => c.Name.StartsWith("t"))
.OrderBy(c => c.Id)
.TakePage(page: 2, count: 2)
.SelectNew(c => new // Append optional extra columns(NameLength, _TotalRowCount) to output result (Note: Return type is non generic IQueryable)
{
NameLength = c.Name.Length,
_TotalRowCount = DBTestDbContext.TotalRowCount()
})
.MapTo <StdRet>() // Map non generic 'IEnumerable' to IEnumerable<StdRet>
.ToList();
Debugger.Break();
var idQueryExp = ((Expression <Func <Student, bool> >)(std => std.Id < 0))
.Or(s => s.Id.IsBetween(10, 20))
.Or(s => s.Id.IsIn(30, 40, 50));
var dbQuery =
new ObjectQueryable <Student>()
.SetUserData("Includes", "City")
//.Where(c => c.Name.Like("%li%"))
.Where(idQueryExp)
//.Where(c => c.Id < 3)
//.Where(c => c.Name.Left(1) == "S")
//.Where(c => c.Name.Reverse().Right(1) != "1")
//.Where(c => c.BirthDate.Value.Date == DateTime.Now.Date)
//.Sort("Name DESC, Id ASC")
//.Where<Student, string>("Name", (s, name) => name != s.Id.ToString())
//.Where<Student, string>("Name", name => name.Contains("m"))
//.Skip(countAccessor: () => db.CURRENT_USER().Length) // Call 'Skip' method with an expression as count parameter.
//.OrderBy(c => c.Id).TakePage(page: 5, count: 10) // Take page 5 (size of each page is 10)
;
var dataResult = db.Student.AsNoTracking()
.Include(dbQuery.GetUserData()["Includes"].ToString())
.ApplyDataQuery(dbQuery)
.ToList();
Debugger.Break();
//Additional methods for EF linq
IQueryable <City> query = new ObjectQueryable <City>();
query = query.Where(c => c.Id.IsBetween(2, 5));
query = query.Where(c => c.Id.IsIn(2, 5, 7));
query = query.Where(c => c.Id.HasFlag(5));
var retlist = db.City.AsNoTracking().ApplyDataQuery(query).ToList();
//delete record(s) with query.
var deleteResult = db.City
.Where(i => i.Id == 60000)
.DeleteDirectly(i => i.Name == null);
//update partial fields record(s) with query.
var updateResult = db.Student
.Where(i => i.Id == 40000)
.UpdateDirectly(() => new Student
{
Name = "Mohsen",
CityRef = null,
BirthDate = DateTime.Today,
});
//call db scalar function
var fnResult = db.fnGetAgeYear(2005);
//using db scalar function in query
var fnResult2 = db.Student.Where(i => i.Id < db.fnGetAgeYear(i.Id)).ToList();
//execute query on db table-valued function
var fnResult3 = db.fnGetNames(1).Select(n => n.Name).Where(n => n.StartsWith("m")).ToList();
//execute query on db table-valued function
var fnResult4 = db.fnGetStudents().Where(s => s.Id > 10).ToList();
//using SQL Server built-in function LEFT
var fnResult5 = db.LEFT("Alireza", 3);
//using SQL Server built-in function in query
var fnResult6 = db.fnGetNames(null).Select(r => db.LEFT(r.Name, 3)).ToList();
//using SQL Server niladic function CURRENT_USER
var fnResult7 = db.ERROR();
//using SQL Server niladic function in query
var fnResult8 = db.fnGetNames(null).Select(r => db.CURRENT_USER()).ToList();
//call db stored procedure with no result.
int?id = 1006;
int?cls = null;
db.spInsertTest(ref id, "Aminzadeh", ref cls);
//call db stored procedure with single result.
int?count = null;
var spResult = db.spGetStudents(ref count).ToList();
//call db stored procedure with multiple results.
string searchKey = "i";
var spMultiResults = db.spFindNamesAndCityNames(ref searchKey);
var spMRResult1 = spMultiResults.GetResult <spGetNamesAndCityNamesResult>(0).ToList();
var spMRResult2 = spMultiResults.GetResult <City>(1).ToList();
//var spMRResult3 = spMultiResults.GetResult<spGetNamesAndCityNamesResult>(2).ToList();
db.City.Delete(1008);
db.SaveChanges();
db.City.Update(() => new City
{
Id = 1009, // Key
Name = "Kerman",
});
db.SaveChanges();
NewTests:
var trans = db.Database.BeginTransaction();
db.LockTable <Student>();
trans.Rollback();
//var udtResult = db.spTestTypes(Enumerable.Range(1, 10).Select(i => new udtIntArray(i)).ToArray());
//var udtResult2 = db.fnTestTypes(Enumerable.Range(1, 10).Select(i => new udtIntStringArray(i, "Name: " + i)).ToArray());
//var udtResult3 = db.fnGetMaxName(Enumerable.Range(1, 10).Select(i => new udtIntStringArray(i, "Ali-" + i)).ToArray());
var filter = new List <udtKeyValueData>
{
new udtKeyValueData("Id", "0", "<>"),
new udtKeyValueData("AND"),
//new udtKeyValueData("cityName", "%ma%", "LIKE"),
new udtKeyValueData("BirthDate", "2009/10/09", "<>"),
};
var sorting = new List <udtKeyValue>
{
new udtKeyValue("name")
};
var spResult200 =
db.spSearchStudent(filter.ToArray(), sorting.ToArray(), null, null)
.ToList();
Debugger.Break();
}
}
