public static void BasicQuery()
{
IQuery <User> q = context.Query <User>();
q.Where(a => a.Id == 1).FirstOrDefault();
/*
* SELECT TOP (1) [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime] FROM [Users] AS [Users] WHERE [Users].[Id] = 1
*/
//可以选取指定的字段
q.Where(a => a.Id == 1).Select(a => new { a.Id, a.Name }).FirstOrDefault();
/*
* SELECT TOP (1) [Users].[Id] AS [Id],[Users].[Name] AS [Name] FROM [Users] AS [Users] WHERE [Users].[Id] = 1
*/
//分页
q.Where(a => a.Id > 0).OrderBy(a => a.Age).Skip(20).Take(10).ToList();
/*
* SELECT TOP (10) [T].[Id] AS [Id],[T].[Name] AS [Name],[T].[Gender] AS [Gender],[T].[Age] AS [Age],[T].[CityId] AS [CityId],[T].[OpTime] AS [OpTime] FROM (SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],ROW_NUMBER() OVER(ORDER BY [Users].[Age] ASC) AS [ROW_NUMBER_0] FROM [Users] AS [Users] WHERE [Users].[Id] > 0) AS [T] WHERE [T].[ROW_NUMBER_0] > 20
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void JTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
IQuery <User> users = context.Query <User>();
IQuery <City> cities = context.Query <City>();
IQuery <Province> provinces = context.Query <Province>();
IJoiningQuery <User, City> user_city = users.InnerJoin(cities, (user, city) => user.CityId == city.Id);
IJoiningQuery <User, City, Province> user_city_province = user_city.InnerJoin(provinces, (user, city, province) => city.ProvinceId == province.Id);
//只获取UserId,CityName,ProvinceName
user_city_province.Select((user, city, province) => new { UserId = user.Id, CityName = city.Name, ProvinceName = province.Name }).Where(a => a.UserId == 1).ToList();
/*
* SELECT [Users].[Id] AS [UserId],[City].[Name] AS [CityName],[Province].[Name] AS [ProvinceName] FROM [Users] AS [Users] INNER JOIN [City] AS [City] ON [Users].[CityId] = [City].[Id] INNER JOIN [Province] AS [Province] ON [City].[ProvinceId] = [Province].[Id] WHERE [Users].[Id] = 1
*/
//可以调用 Select 方法返回一个 IQuery<T> 对象
var view = user_city_province.Select((user, city, province) => new { User = user, City = city, Province = province });
//查出一个用户及其隶属的城市和省份
view.Where(a => a.User.Id == 1).ToList();
/*
* SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[City].[Id] AS [Id0],[City].[Name] AS [Name0],[City].[ProvinceId] AS [ProvinceId],[Province].[Id] AS [Id1],[Province].[Name] AS [Name1] FROM [Users] AS [Users] INNER JOIN [City] AS [City] ON [Users].[CityId] = [City].[Id] INNER JOIN [Province] AS [Province] ON [City].[ProvinceId] = [Province].[Id] WHERE [Users].[Id] = 1
*/
////这时候也可以选取指定的字段
//view.Where(a => a.User.Id == 1).Select(a => new { UserId = a.User.Id, CityName = a.City.Name, ProvinceName = a.Province.Name }).ToList();
///*
// * SELECT [Users].[Id] AS [UserId],[City].[Name] AS [CityName],[Province].[Name] AS [ProvinceName] FROM [Users] AS [Users] INNER JOIN [City] AS [City] ON [Users].[CityId] = [City].[Id] INNER JOIN [Province] AS [Province] ON [City].[ProvinceId] = [Province].[Id] WHERE [Users].[Id] = 1
// */
////假设已经有5个表建立了连接的对象为 jq_q1_q5
//IJoiningQuery<T1, T2, T3, T4, T5> jq_q1_q5 = null;
////jq_q1_q5 调用 Select 方法，返回一个包含 T1-T5 的 IQuery<T> 对象 view_q1_q5
//var view_q1_q5 = jq_q1_q5.Select((t1, t2, t3, t4, t5) => new { T1 = t1, T2 = t2, T3 = t3, T4 = t4, T5 = t5 });
////假设第6个表的 IQuery<T6> 对象为 q6
//IQuery<T6> q6 = null;
////这时，view_q1_q5 与 q6 建立连接，返回 IJoiningQuery 对象 jq
//var jq = view_q1_q5.InnerJoin(q6, (t1_t5, t6) => t1_t5.T5.XX == t6.XXX);
////然后我们调用 jq 的 Select 方法，返回一个包含 T1-T6 的 IQuery<T> 对象 q。
////q 又是一个 IQuery<T> 对象，泛型参数为包含 T1-T6 所有信息的匿名对象，拿到它，我们就可以为所欲为了。
//var q = jq.Select((t1_t5, t6) => new { T1 = t1_t5.T1, T2 = t1_t5.T2, T3 = t1_t5.T3, T4 = t1_t5.T4, T5 = t1_t5.T5, T6 = t6 });
////可以直接查出数据库中 T1-T6 的所有信息
//q.ToList();
////也可以选取 T1-T6 中我们想要的字段
//q.Select(a => new { a.T1.xx, a.T2.xx, a.T3.xx /*...*/}).ToList();
ConsoleHelper.WriteLineAndReadKey();
}
public void Execute(IJobExecutionContext context)
{
//IDbCommandInterceptor interceptor = new DbCommandInterceptor();
//dbcontext.Session.AddInterceptor(interceptor);
DateTime dt = DateTime.Now;
string shortdate = dt.ToString("yyyy-MM-dd");
IQuery <Page> cpq = dbcontext.Query <Page>();
IQuery <Chapter> cq = dbcontext.Query <Chapter>();
List <Page> plst = cpq.Where(a => a.pagelocal.Length == 0).Take(20).ToList();
HttpWebHelper web = new HttpWebHelper();
foreach (var p in plst)
{
try
{
Stream stream = web.GetStream("http://cdn.sns.dongmanmanhua.cn/20150119_288/1421677325732TxLNo_JPEG/thumbnail_ipad.jpg");
Image img = Image.FromStream(stream);
stream.Close();
string filePath = AppDomain.CurrentDomain.BaseDirectory + "DownLoadImgs/" + p.Id + ".jpg";
img.Save(filePath, System.Drawing.Imaging.ImageFormat.Jpeg);
string localimg = UcHelper.uploadFile("Page/" + p.Id + ".jpg", filePath);
p.pagelocal = localimg;
p.modify = dt;
dbcontext.Update(p);
dbcontext.Update <Chapter>(a => a.Id == p.chapterid, a => new Chapter()
{
downstatus = DownChapter.完图片,
modify = dt
});
File.Delete(filePath);
}
catch (Exception ex)
{
logger.Error(ex.Message);
//Chapter chapter = cq.Where(x => x.Id == p.chapterid).FirstOrDefault();
//chapter.retry = chapter.retry + 1;
//chapter.modify = dt;
//dbcontext.Update(chapter);
dbcontext.Update <Chapter>(a => a.Id == p.chapterid, a => new Chapter()
{
retry = a.retry + 1,
modify = dt
});
Err_PageJob err = new Err_PageJob();
err.imgurl = p.pagesource;
err.source = p.source;
err.errtype = ErrPage.限制访问;
err.modify = dt;
err.shortdate = shortdate;
err.message = ex.Message;
err = dbcontext.Insert(err);
continue;
}
}
}
public static void user()
{
IQuery <User> q = context.Query <User>().AddWhere(" Name>'1'");
q = q.AddWhere("ID=1");
q = q.Where(a => a.Id == 1);
q.ToList();
}
public static void Query()
{
object ret = null;
var q = context.Query <TestEntity>();
ret = q.Where(a => a.Id > 0).ToList();
ret = q.Where(a => a.Id > 0).Skip(1).Take(999).ToList();
ret = q.Where(a => a.Id > 0).OrderBy(a => a.F_Int32).ThenBy(a => a.F_Int64).Skip(1).Take(999).ToList();
ConsoleHelper.WriteLineAndReadKey();
}
public static void ChloeQueryTest(int takeCount)
{
using (MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString))
{
if (takeCount == 1)
{
var list = context.Query <TestEntity>().Where(b => b.Id <id2 && b.Id> id).Take(takeCount).ToList();
}
else
{
var list = context.Query <TestEntity>().Take(takeCount).ToList();
}
}
}
public void ToPaginableTest()
{
using (var db = new MsSqlContext(connectionString))
{
var list = db.Query <Int32Sample>().ToPaginable(9);
var page = list.GetPage(2);
page.TotalPageCount.ShouldBe(24);
page.TotalMemberCount.ShouldBe(210);
page.CurrentPageNumber.ShouldBe(2);
page.PageSize.ShouldBe(9);
page.CurrentPageSize.ShouldBe(9);
page.HasNext.ShouldBeTrue();
page.HasPrevious.ShouldBeTrue();
page[0].Value.Id.ShouldBe(10);
page[1].Value.Id.ShouldBe(11);
page[2].Value.Id.ShouldBe(12);
page[3].Value.Id.ShouldBe(13);
page[4].Value.Id.ShouldBe(14);
page[5].Value.Id.ShouldBe(15);
page[6].Value.Id.ShouldBe(16);
page[7].Value.Id.ShouldBe(17);
page[8].Value.Id.ShouldBe(18);
}
}
public static void GroupJion()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
IQuery <User> users = context.Query <User>();
IQuery <City> cities = context.Query <City>();
var gq = users.GroupBy(a => a.CityId).Select(a => new { a.CityId, MinAge = Sql.Min(a.Age) });
cities.LeftJoin(gq, (city, g) => city.Id == g.CityId).Select((city, g) => new { City = city, MinAge = g.MinAge }).ToList();
/*
* SELECT [T].[MinAge] AS [MinAge],[City].[Id] AS [Id],[City].[Name] AS [Name],[City].[ProvinceId] AS [ProvinceId] FROM [City] AS [City] LEFT JOIN (SELECT [Users].[CityId] AS [CityId],MIN([Users].[Age]) AS [MinAge] FROM [Users] AS [Users] GROUP BY [Users].[CityId]) AS [T] ON [City].[Id] = [T].[CityId]
*/
ConsoleHelper.WriteLineAndReadKey();
}
/*sql script:
* CREATE TABLE [dbo].[ExtensionMappingType](
* [Id] [int] IDENTITY(1,1) NOT NULL,
* [Name] [nvarchar](100) NULL,
* [F_Char] [nvarchar](1) NULL,
* [F_Time] [time](7) NULL,
* CONSTRAINT [PK_ExtensionMappingType] PRIMARY KEY CLUSTERED
* (
* [Id] ASC
* )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
* ) ON [PRIMARY]
*/
public static void RunDemo()
{
//step 1:
/* 原生 Chloe 不支持 char 和 TimeSpan 类型映射，需要我们自己注册，注册映射类必须在程序启动时进行 */
MappingTypeSystem.Configure(typeof(char), DbType.StringFixedLength);
MappingTypeSystem.Configure(typeof(TimeSpan), DbType.Time);
//step 2:
/* 因为我们新增了 MappingType，所以需要对原生的 SqlConnection、SqlServerCommand、SqlServerDataReader、SqlServerParameter 包装处理，所以，我们需要自个儿实现 IDbConnectionFactory 工厂 */
SqlServerDbConnectionFactory sqlServerDbConnectionFactory = new SqlServerDbConnectionFactory(DbHelper.ConnectionString);
MsSqlContext context = new MsSqlContext(sqlServerDbConnectionFactory);
/* 经过上述封装，我们就可以支持 char 和 TimeSpan 类型映射了 */
ExtensionMappingType entity = new ExtensionMappingType();
entity.Name = "test";
entity.F_Char = 'A';
entity.F_Time = TimeSpan.FromHours(12);
context.Insert(entity);
Console.WriteLine(entity.Id);
TimeSpan ts = TimeSpan.FromHours(12);
ExtensionMappingType model = context.Query <ExtensionMappingType>().Where(a => a.F_Time == ts && a.Id == entity.Id).First();
Console.WriteLine(model.Id == entity.Id);
Console.WriteLine("Yeah!!");
Console.ReadKey();
}
//[TestMethod]
public static void QueryTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
context.PagingMode = PagingMode.OFFSET_FETCH;
IQuery <User> q = context.Query <User>();
//var s = q.Select(a => (decimal)a.Id).ToString();
//var xx = q.Select(a => (decimal)a.Id).ToList();
object ret = null;
ret = q.Where(a => a.Id > 0).FirstOrDefault();
ret = q.Where(a => a.Id > 0).Where(a => a.Name.Contains("lu")).ToList();
ret = q.Where(a => a.Id > 0).OrderBy(a => a.Id).ToList();
ret = q.Where(a => a.Id > 0).OrderBy(a => a.Id).ThenByDesc(a => a.Age).ToList();
ret = q.Where(a => a.Id > 0).Skip(1).ToList();
ret = q.Where(a => a.Id > 0).Take(999).ToList();
ret = q.Where(a => a.Id > 0).Take(999).OrderBy(a => a.Age).ToList();
ret = q.Where(a => a.Id > 0).Skip(1).Take(999).ToList();
ret = q.Where(a => a.Id > 0).OrderBy(a => a.Id).ThenByDesc(a => a.Age).Skip(1).Take(999).ToList();
ret = q.Where(a => a.Id > 0).OrderBy(a => a.Id).ThenByDesc(a => a.Age).Skip(1).Take(999).Where(a => a.Id > -100).ToList();
ret = q.Select(a => new { Name1 = a.Name, Name2 = a.Name }).ToList();
q.Select(a => new { a.Id, a.Name, a.Age }).ToList();
q.Select(a => new User()
{
Id = a.Id, Name = a.Name, Age = a.Age
}).ToList();
Console.WriteLine(1);
Console.ReadKey();
}
public static void GroupQueryTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
object ret = null;
var q = context.Query <User>();
var r = q.GroupBy(a => a.Id).Having(a => a.Id > 1).Select(a => new { a.Id, Count = DbFunctions.Count(), Sum = DbFunctions.Sum(a.Id), Max = DbFunctions.Max(a.Id), Min = DbFunctions.Min(a.Id), Avg = DbFunctions.Average(a.Id) }).ToList();
q.GroupBy(a => a.Age).Having(a => a.Age > 1).Select(a => new { a.Age, Count = DbFunctions.Count(), Sum = DbFunctions.Sum(a.Age), Max = DbFunctions.Max(a.Age), Min = DbFunctions.Min(a.Age), Avg = DbFunctions.Average(a.Age) }).ToList();
var r1 = q.GroupBy(a => a.Age).Having(a => DbFunctions.Count() > 0).Select(a => new { a.Age, Count = DbFunctions.Count(), Sum = DbFunctions.Sum(a.Age), Max = DbFunctions.Max(a.Age), Min = DbFunctions.Min(a.Age), Avg = DbFunctions.Average(a.Age) }).ToList();
var g = q.GroupBy(a => a.Gender);
//g = g.ThenBy(a => a.Name);
//g = g.Having(a => a.Id > 0);
//g = g.Having(a => a.Name.Length > 0);
var gq = g.Select(a => new { Count = DbFunctions.Count() });
//gq = gq.Skip(1);
//gq = gq.Take(100);
//gq = gq.Where(a => a > -1);
ret = gq.ToList();
var c = gq.Count();
ConsoleHelper.WriteLineAndReadKey();
}
/// <summary>
/// 操作数据库
/// </summary>
/// <param name="args"></param>
static void Main(string[] args)
{
#region 使用Chloe封装好的方式
string connString = "database=test;server=.;User=sa;password=sa;";
MsSqlContext context = new MsSqlContext(connString);
//MsSqlContext 对象默认使用 ROWNUMBER 的分页方式，如果您的数据库是 SqlServer2012 或更高版本，可以切换使用 OFFSET FETCH 分页方式
context.PagingMode = PagingMode.OFFSET_FETCH;
var query = context.Query <Class>().Where(a => a.class_id == 7).FirstOrDefault();
Console.WriteLine($"{query}");
#endregion
#region 重新写扩展方法调用的方式,使用的是ASP.NET CORE 配置 Service
////查询Class表中的所有数据
//var list = new Test.DB.DB().FindList<Class>();
////循环输出查看
//foreach (var item in list)
//{
// Console.WriteLine($"{item.class_id},{item.class_name}");
//}
#endregion
Console.ReadKey();
}
public static void TrackingTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
object ret = null;
var q = context.Query <User>();
q = q.AsTracking();
User user = q.First();
ret = context.Update(user);
Console.WriteLine(ret);
context.TrackEntity(user);
user.Name = user.Name + "1";
user.Age = user.Age;
user.Gender = null;
ret = context.Update(user);
Console.WriteLine(ret);
ret = context.Update(user);
Console.WriteLine(ret);
ConsoleHelper.WriteLineAndReadKey();
}
static void ChloeQueryTest(int takeCount)
{
using (MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString))
{
var list = context.Query <TestEntity>().Take(takeCount).ToList();
}
}
//[TestMethod]
public static void QueryTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
var q = context.Query <User>();
object ret = null;
q.Where(a => a.Id > 0).FirstOrDefault();
ret = q.Where(a => a.Id > 0).ToList();
ret = q.Where(a => a.Id > 0).OrderBy(a => a.Id).ToList();
ret = q.Where(a => a.Id > 0).OrderBy(a => a.Id).ThenByDesc(a => a.Age).ToList();
ret = q.Where(a => a.Id > 0).Skip(1).ToList();
ret = q.Where(a => a.Id > 0).Take(999).ToList();
ret = q.Where(a => a.Id > 0).Take(999).OrderBy(a => a.Age).ToList();
ret = q.Where(a => a.Id > 0).Skip(1).Take(999).ToList();
ret = q.Where(a => a.Id > 0).OrderBy(a => a.Id).ThenByDesc(a => a.Age).Skip(1).Take(999).ToList();
ret = q.Where(a => a.Id > 0).OrderBy(a => a.Id).ThenByDesc(a => a.Age).Skip(1).Take(999).Where(a => a.Id > -100).ToList();
ret = q.Select(a => new { Name1 = a.Name, Name2 = a.Name }).ToList();
Console.WriteLine(1);
Console.ReadKey();
}
public void Execute(IJobExecutionContext context)
{
try
{
DateTime dt = DateTime.Now;
string shortdate = dt.ToString("yyyy-MM-dd");
var bookdata = _helper.Get(null, "http://ac.qq.com/Comic/all/search/time/vip/3/page/1");
IQuery <VIPFreeComic> vfcq = dbcontext.Query <VIPFreeComic>();
string pattern_page = "共<em>(?<key1>.*?)</em>个结果";
MatchCollection matches_page = Regex.Matches(bookdata, pattern_page, RegexOptions.IgnoreCase | RegexOptions.Singleline);
int pagecount = (int)Math.Ceiling(int.Parse(matches_page[0].Groups[1].Value) / 12.0);
List <VIPFreeComic> vfclst = new List <VIPFreeComic>();
for (int i = 1; i <= pagecount; i++)
{
var bookdata2 = _helper.Get(null, "http://ac.qq.com/Comic/all/search/time/vip/3/page/" + i);
bookdata2 = StringHelper.MergeSpace(bookdata2);
string pattern2 = "<h3 class=\"ret-works-title clearfix\"> <a href=\"(?<key1>.*?)\" target=\"_blank\" title=\"(?<key2>.*?)\">(?<key3>.*?)</a>";
MatchCollection matches2 = Regex.Matches(bookdata2, pattern2, RegexOptions.IgnoreCase | RegexOptions.Singleline);
for (int j = 0; j < matches2.Count; j++)
{
string bookurl = "http://ac.qq.com" + matches2[j].Groups["key1"].Value;
string comicname = matches2[j].Groups["key2"].Value;
string comicid = (int)Source.QQ + "_" + bookurl.Split('/').LastOrDefault();
vfclst.Add(new VIPFreeComic()
{
bookurl = bookurl,
comicid = comicid,
comicname = comicname,
modify = dt,
shortdate = shortdate,
source = Source.QQ
});
}
}
List <VIPFreeComic> vfcdblst = vfcq.Where(x => x.source == Source.QQ).ToList();
if (vfclst.Count > 0)
{
List <VIPFreeComic> delete = vfcdblst.Except(vfclst, new VIPFreeComic_Comparer()).ToList();
List <VIPFreeComic> add = vfclst.Except(vfcdblst, new VIPFreeComic_Comparer()).ToList();
if (delete.Count > 0)
{
List <string> deleteidlst = delete.Select(x => x.comicid).ToList();
dbcontext.Delete <VIPFreeComic>(x => deleteidlst.Contains(x.comicid));
}
if (add.Count > 0)
{
dbcontext.BulkInsert(add);
}
}
}
catch (Exception ex)
{
throw;
}
}
public void Execute(IJobExecutionContext context)
{
//IDbCommandInterceptor interceptor = new DbCommandInterceptor();
//dbcontext.Session.AddInterceptor(interceptor);
DateTime dt = DateTime.Now;
string shortdate = dt.ToString("yyyy-MM-dd");
IQuery <Comic> comicq = dbcontext.Query <Comic>();
IQuery <Chapter> cpq = dbcontext.Query <Chapter>();
List <Chapter> plst = cpq.Where(a => a.chaptersource.Length != 0 && a.chapterlocal.Length == 0).TakePage(1, 20).ToList();
HttpWebHelper web = new HttpWebHelper();
foreach (var p in plst)
{
try
{
string refer = "";
if (p.source == Source.dongmanmanhua)
{
var comic = comicq.Where(x => x.comicid == p.comicid).FirstOrDefault();
refer = comic.bookurl;
}
Stream stream = web.GetStream(p.chaptersource, 300, "", null, refer, null);
Image img = Image.FromStream(stream);
stream.Close();
string filePath = AppDomain.CurrentDomain.BaseDirectory + "DownLoadImgs/" + p.Id + ".jpg";
img.Save(filePath, System.Drawing.Imaging.ImageFormat.Jpeg);
string localimg = UcHelper.uploadFile("Chapter/" + p.Id + ".jpg", filePath);
p.chapterlocal = localimg;
p.modify = dt;
dbcontext.Update(p);
}
catch (Exception ex)
{
Err_ChapterJob err = new Err_ChapterJob();
err.bookurl = p.chapterurl;
err.errtype = ErrChapter.图片出错;
err.modify = dt;
err.shortdate = shortdate;
err.message = ex.Message;
err = dbcontext.Insert(err);
continue;
}
}
}
public static void JoinQueryTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
IQuery <User> q = context.Query <User>();
IQuery <User> q1 = context.Query <User>();
IQuery <User> q2 = context.Query <User>();
object ret = null;
ret = q.InnerJoin(context.Query <User>(), (a, b) => a.Id == b.Id).Select((a, b) => new { A = a, B = b }).ToList();
ret = q.InnerJoin(context.Query <User>().Select(a => new { a.Id, a.Name }), (a, b) => a.Id == b.Id).Select((a, b) => new { A = a, B = b }).ToList();
ret = q.LeftJoin(context.Query <User>().Select(a => new { a.Id, a.Name }), (a, b) => a.Id == b.Id + 1).Select((a, b) => new { A = a, B = b }).ToList();
ret = q.RightJoin(context.Query <User>().Where(a => a.Id <= 20).Select(a => new { a.Id, a.Name }), (a, b) => a.Id == b.Id + 1).Select((a, b) => new { A = a, B = b }).ToList();
ret = q.InnerJoin(q1, (a, b) => a.Id == b.Id).InnerJoin(q2, (a, b, c) => a.Name == c.Name).Select((a, b, c) => new { A = a, B = b, C = c }).ToList();
ret = q.InnerJoin(q1, (a, b) => a.Id == b.Id).LeftJoin(q2, (a, b, c) => a.Name == c.Name).RightJoin(q, (a, b, c, d) => a.Id == d.Id + 1).Select((a, b, c, d) => new { A = a, B = b, C = c, D = d }).ToList();
ret = q.InnerJoin(q1, (a, b) => a.Id == b.Id).InnerJoin(q2, (a, b, c) => a.Name == c.Name).RightJoin(q, (a, b, c, d) => a.Id == d.Id + 1).Select((a, b, c, d) => new { A = a, D = d }).ToList();
ret = q.InnerJoin(q1, (a, b) => a.Id == b.Id).InnerJoin(q2.Where(a => a.Id > 0).Select(a => a.Id), (a, b, c) => a.Id == c).RightJoin(q, (a, b, c, d) => a.Id == d.Id + 1).Select((a, b, c, d) => new { a, C = (int?)c }).ToList();
ret = q.InnerJoin(q1, (a, b) => a.Id == b.Id).LeftJoin(q2.Where(a => a.Id > 0).Select(a => a.Id), (a, b, c) => a.Id == c).FullJoin(q, (a, b, c, d) => a.Id == d.Id + 1).Select((a, b, c, d) => new { a, C = (int?)c }).ToList();
q.InnerJoin(q1, (a, b) => a.Id == b.Id).Select((a, b) => new { a, b }).Where(a => a.a.Id > 0).ToList();
Console.WriteLine(1);
}
public static void PredicateTest()
{
object ret = null;
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
var q = context.Query <User>();
List <int> ids = new List <int>();
ids.Add(1);
ids.Add(2);
ids.Add(2);
string name = "lu";
string nullString = null;
//name = null;
bool b = false;
bool b1 = true;
//ret = q.Where(a => true).ToList();
//ret = q.Where(a => a.Id == FeatureTest.ID).ToList();
//ret = q.Where(a => a.Id == FeatureTest.ID || a.Id > 1).ToList();
//ret = q.Where(a => a.Id == 1 && a.Name == name && a.Name == nullString && a.Id == FeatureTest.ID).ToList();
//ret = q.Where(a => ids.Contains(a.Id)).ToList();
//ret = q.Where(a => !b == (a.Id > 0)).ToList();
//ret = q.Where(a => a.Id > 0).Where(a => a.Id == 1).ToList();
//ret = q.Where(a => !(a.Id > 10)).ToList();
//ret = q.Where(a => !(a.Name == name)).ToList();
//ret = q.Where(a => a.Name != name).ToList();
//ret = q.Where(a => a.Name == name).ToList();
//ret = q.Where(a => (a.Name == name) == (a.Id > 0)).ToList();
ret = q.Where(a => a.Name == (a.Name ?? name)).ToList();
ret = q.Select(a => new { Name = a.Name ?? "abc", Age = a.Age ?? 17, A = a.Age, B = (a.Age ?? 17) < 18 }).ToList();
//ret = q.Where(a => (a.Age == null ? 0 : 1) == 1).ToList();
//ret = q.Select(a => b & b1).ToList();
//ret = q.Select(a => a.Id & 1).ToList();
//ret = q.Select(a => new { Id = a.Id, And = a.Id & 1, And1 = a.Id & 1 & a.Id, Or = a.Id | 1, B = b & b1 }).ToList();
//var xxx = q.ToList().Select(a => new { Id = a.Id, And = a.Id & 1, And1 = a.Id & 1 & a.Id, Or = a.Id | 1 }).ToList();
//ret = q.Where(a => b & true).ToList();
//ret = q.Where(a => b | true).ToList();
//ret = q.Where(a => b || true).ToList();
//ret = q.Where(a => b & b1).ToList();
//ret = q.Where(a => b | b1).ToList();
//ret = q.Where(a => b || b1).ToList();
ret = q.Select(a => a.Name + "-123").ToList();
Console.WriteLine(1);
Console.ReadKey();
}
static void Main(string[] args)
{
var context = new MsSqlContext("server=.;database=orderdb;uid=sa;pwd=1;");
var orders = context.Query <Orders>().ToList();
foreach (var order in orders)
{
Console.WriteLine(order);
}
}
public static void AFTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
IQuery <User> q = context.Query <User>();
q.Select(a => Sql.Count()).First();
/*
* SELECT TOP (1) COUNT(1) AS [C] FROM [Users] AS [Users]
*/
q.Select(a => new { Count = Sql.Count(), LongCount = Sql.LongCount(), Sum = Sql.Sum(a.Age), Max = Sql.Max(a.Age), Min = Sql.Min(a.Age), Average = Sql.Average(a.Age) }).First();
/*
* SELECT TOP (1) COUNT(1) AS [Count],COUNT_BIG(1) AS [LongCount],SUM([Users].[Age]) AS [Sum],MAX([Users].[Age]) AS [Max],MIN([Users].[Age]) AS [Min],CAST(AVG([Users].[Age]) AS FLOAT) AS [Average] FROM [Users] AS [Users]
*/
var count = q.Count();
/*
* SELECT COUNT(1) AS [C] FROM [Users] AS [Users]
*/
var longCount = q.LongCount();
/*
* SELECT COUNT_BIG(1) AS [C] FROM [Users] AS [Users]
*/
var sum = q.Sum(a => a.Age);
/*
* SELECT SUM([Users].[Age]) AS [C] FROM [Users] AS [Users]
*/
var max = q.Max(a => a.Age);
/*
* SELECT MAX([Users].[Age]) AS [C] FROM [Users] AS [Users]
*/
var min = q.Min(a => a.Age);
/*
* SELECT MIN([Users].[Age]) AS [C] FROM [Users] AS [Users]
*/
var avg = q.Average(a => a.Age);
/*
* SELECT CAST(AVG([Users].[Age]) AS FLOAT) AS [C] FROM [Users] AS [Users]
*/
ConsoleHelper.WriteLineAndReadKey();
}
public void Execute(IJobExecutionContext context)
{
DateTime dt = DateTime.Now;
string shortdate = dt.ToString("yyyy-MM-dd");
IQuery <Chapter> cpq = dbcontext.Query <Chapter>();
IQuery <Comic> cq = dbcontext.Query <Comic>();
IQuery <Notice> nq = dbcontext.Query <Notice>();
List <Notice> nqlst = nq.Where(x => x.noticestatus == NoticeStatus.等待处理).OrderBy(x => x.Id).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();//Select(x => x).
if (nqlst.Count < pageSize)
{
pageIndex = 1;
}
else
{
pageIndex++;
}
foreach (var n in nqlst)
{
if (n.noticetype == NoticeType.目录变更)
{
List <Chapter> cplst = cpq.Where(x => x.comicid == n.noticeid && x.downstatus != DownChapter.完图片).ToList();
if (cplst.Count == 0)
{
n.noticestatus = NoticeStatus.等待发送;
n.modify = dt;
dbcontext.Update(n);
}
}
else if (n.noticetype == NoticeType.章节更新)
{
Chapter chapter = cpq.Where(x => x.chapterid == n.noticeid).FirstOrDefault();
Notice notice = nq.Where(x => x.noticeid == chapter.comicid && x.noticetype == NoticeType.目录变更 && x.noticestatus != NoticeStatus.已发送).FirstOrDefault();
if (notice == null)
{
n.noticestatus = NoticeStatus.等待发送;
n.modify = dt;
dbcontext.Update(n);
}
}
}
}
public void Execute(IJobExecutionContext context)
{
DateTime dt = DateTime.Now;
string shortdate = dt.ToString("yyyy-MM-dd");
IQuery <Page> cpq = dbcontext.Query <Page>();
IQuery <Chapter> cq = dbcontext.Query <Chapter>();
List <Chapter> cqlst = cq.Where(x => x.downstatus == DownChapter.处理完链接).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
if (cqlst.Count < pageSize)
{
pageIndex = 1;
}
else
{
pageIndex++;
}
List <string> cidlst = cqlst.Select(x => x.chapterid).ToList();
List <Page> pagelst = cpq.Where(x => cidlst.Contains(x.chapterid)).ToList();
List <int> waitidlst = new List <int>();
foreach (var c in cqlst)
{
List <Page> pagedownlst = pagelst.Where(x => x.chapterid == c.chapterid).ToList();
if (pagedownlst.Count > 0)
{
List <Page> pagenulllst = pagedownlst.Where(x => x.pagelocal == "").ToList();
if (pagenulllst.Count == 0)
{
waitidlst.Add(c.Id);
}
}
//c.downstatus = pagenulllst.Count > 0 ? DownChapter.处理完链接 : DownChapter.上传完图片;
//c.modify = dt;
//dbcontext.Update(c);
}
dbcontext.Update <Chapter>(a => waitidlst.Contains(a.Id), a => new Chapter()
{
downstatus = DownChapter.完图片,
modify = dt
});
}
static void ChloeQueryTest(int takeCount, int loops)
{
using (MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString))
{
for (int i = 0; i < loops; i++)
{
int id = 0;
var list = context.Query <TestEntity>().Where(a => a.Id > id).Take(takeCount).ToList();
}
}
}
public static void MappingTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
IQuery <User> q = context.Query <User>();
string n = "";
Expression <Func <User, bool> > p = a => a.Name == n;
//q.Where(a => a.Name == n);
var r = q.ToList();
Console.WriteLine(1);
}
public static void AggregateFunctionTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
var q = context.Query <User>();
//var xxx = q.Select(a => Sql.Count()).ToList().First();
var xxx = q.Select(a => new { MaxTime = Sql.Max(a.OpTime), MinTime = Sql.Min(a.OpTime) }).ToList();
q = q.Where(a => a.Id > 0);
var count = q.Count();
var longCount = q.LongCount();
var sum = q.Sum(a => a.Age);
var max = q.Max(a => a.Age);
var min = q.Min(a => a.Age);
var avg = q.Average(a => a.Age);
decimal dc = 1;
//Console.WriteLine(q.Sum(a => (decimal)1));
Console.WriteLine(q.Select(a => Sql.Average(1)).First());
Console.WriteLine(q.Select(a => Sql.Average(dc)).First());
//Console.WriteLine(q.Sum(a => 1));
//Console.WriteLine(q.Sum(a => 1L));
Console.WriteLine(q.Sum(a => dc));
//Console.WriteLine(q.Sum(a => 1D));
//Console.WriteLine(q.Sum(a => 1F));
//Console.WriteLine(q.Max(a => 1));
//Console.WriteLine(q.Max(a => 1L));
Console.WriteLine(q.Max(a => dc));
//Console.WriteLine(q.Max(a => 1D));
//Console.WriteLine(q.Max(a => 1F));
//Console.WriteLine(q.Min(a => 1));
//Console.WriteLine(q.Min(a => 1L));
Console.WriteLine(q.Min(a => dc));
//Console.WriteLine(q.Min(a => 1D));
//Console.WriteLine(q.Min(a => 1F));
//Console.WriteLine(q.Average(a => 1));
//Console.WriteLine(q.Average(a => 1L));
Console.WriteLine(q.Average(a => dc));
//Console.WriteLine(q.Average(a => 1D));
//Console.WriteLine(q.Average(a => 1F));
ConsoleHelper.WriteLineAndReadKey();
}
public static void GQTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
IQuery <User> q = context.Query <User>();
IGroupingQuery <User> g = q.Where(a => a.Id > 0).GroupBy(a => a.Age);
g = g.Having(a => a.Age > 1 && Sql.Count() > 0);
g.Select(a => new { a.Age, Count = Sql.Count(), Sum = Sql.Sum(a.Age), Max = Sql.Max(a.Age), Min = Sql.Min(a.Age), Avg = Sql.Average(a.Age) }).ToList();
/*
* SELECT [Users].[Age] AS [Age],COUNT(1) AS [Count],SUM([Users].[Age]) AS [Sum],MAX([Users].[Age]) AS [Max],MIN([Users].[Age]) AS [Min],CAST(AVG([Users].[Age]) AS FLOAT) AS [Avg] FROM [Users] AS [Users] WHERE [Users].[Id] > 0 GROUP BY [Users].[Age] HAVING ([Users].[Age] > 1 AND COUNT(1) > 0)
*/
}
public static void AggregateFunctionTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
var q = context.Query<User>();
q = q.Where(a => a.Id > 0);
var xxx = q.Select(a => DbFunctions.Count()).First();
q.Select(a => new { Count = DbFunctions.Count(), LongCount = DbFunctions.LongCount(), Sum = DbFunctions.Sum(a.Age), Max = DbFunctions.Max(a.Age), Min = DbFunctions.Min(a.Age), Average = DbFunctions.Average(a.Age) }).First();
var count = q.Count();
var longCount = q.LongCount();
var sum = q.Sum(a => a.Age);
var max = q.Max(a => a.Age);
var min = q.Min(a => a.Age);
var avg = q.Average(a => a.Age);
Console.WriteLine(1);
}
public static void CTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
IQuery <User> q = context.Query <User>();
//var users = q.Select(a => new User() { U = a }).ToList();
//var r = q.Select(a => new { Id = a.Id, Id1 = a.Id }).ToList();
//var r1 = q.Where(a => a.Id > 0).OrderBy(a => a.Id).Skip(1).Take(100).ToList();
//var r2 = q.Where(a => a.Id > 0).OrderBy(a => a.Id).Skip(1).Take(100).Select(a => new { a.Id, a.Name }).ToList();
var r3 = q.Select(a => new { B = a.Id > 5 }).ToList();
Console.WriteLine(1);
}
public static void CTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
IQuery<User> q = context.Query<User>();
//var users = q.Select(a => new User() { U = a }).ToList();
//var r = q.Select(a => new { Id = a.Id, Id1 = a.Id }).ToList();
//var r1 = q.Where(a => a.Id > 0).OrderBy(a => a.Id).Skip(1).Take(100).ToList();
//var r2 = q.Where(a => a.Id > 0).OrderBy(a => a.Id).Skip(1).Take(100).Select(a => new { a.Id, a.Name }).ToList();
var r3 = q.Select(a => new { B = a.Id > 5 }).ToList();
Console.WriteLine(1);
}
public static void AggregateFunctionTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
var q = context.Query <User>();
q = q.Where(a => a.Id > 0);
var xxx = q.Select(a => DbFunctions.Count()).First();
q.Select(a => new { Count = DbFunctions.Count(), LongCount = DbFunctions.LongCount(), Sum = DbFunctions.Sum(a.Age), Max = DbFunctions.Max(a.Age), Min = DbFunctions.Min(a.Age), Average = DbFunctions.Average(a.Age) }).First();
var count = q.Count();
var longCount = q.LongCount();
var sum = q.Sum(a => a.Age);
var max = q.Max(a => a.Age);
var min = q.Min(a => a.Age);
var avg = q.Average(a => a.Age);
Console.WriteLine(1);
}
public static void QTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
IQuery <User> q = context.Query <User>();
q.Where(a => a.Id > 0).FirstOrDefault();
q.Where(a => a.Id > 0).ToList();
q.Where(a => a.Id > 0).OrderBy(a => a.Age).ToList();
q.Where(a => a.Id > 0).Take(999).OrderBy(a => a.Age).ToList();
//分页，避免生成的 sql 语句太长，占篇幅，只选取 Id 和 Name 两个字段
q.Where(a => a.Id > 0).OrderBy(a => a.Age).ThenByDesc(a => a.Id).Select(a => new { a.Id, a.Name }).Skip(1).Take(999).ToList();
/*
* SELECT TOP (999) [T].[Id] AS [Id],[T].[Name] AS [Name] FROM (SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],ROW_NUMBER() OVER(ORDER BY [Users].[Age] ASC,[Users].[Id] DESC) AS [ROW_NUMBER_0] FROM [Users] AS [Users] WHERE [Users].[Id] > 0) AS [T] WHERE [T].[ROW_NUMBER_0] > 1
*/
//如果需要多个条件的话
q.Where(a => a.Id > 0).Where(a => a.Name.Contains("lu")).ToList();
/*
* SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime] FROM [Users] AS [Users] WHERE ([Users].[Id] > 0 AND [Users].[Name] LIKE '%' + N'lu' + '%')
*/
//选取指定字段
q.Select(a => new { a.Id, a.Name, a.Age }).ToList();
//或者
q.Select(a => new User()
{
Id = a.Id, Name = a.Name, Age = a.Age
}).ToList();
/*
* SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Age] AS [Age] FROM [Users] AS [Users]
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void JoinQueryTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
IQuery<User> q = context.Query<User>();
IQuery<User> q1 = context.Query<User>();
IQuery<User> q2 = context.Query<User>();
object ret = null;
ret = q.InnerJoin(context.Query<User>(), (a, b) => a.Id == b.Id).Select((a, b) => new { A = a, B = b }).ToList();
ret = q.InnerJoin(context.Query<User>().Select(a => new { a.Id, a.Name }), (a, b) => a.Id == b.Id).Select((a, b) => new { A = a, B = b }).ToList();
ret = q.LeftJoin(context.Query<User>().Select(a => new { a.Id, a.Name }), (a, b) => a.Id == b.Id + 1).Select((a, b) => new { A = a, B = b }).ToList();
ret = q.RightJoin(context.Query<User>().Where(a => a.Id <= 20).Select(a => new { a.Id, a.Name }), (a, b) => a.Id == b.Id + 1).Select((a, b) => new { A = a, B = b }).ToList();
ret = q.InnerJoin(q1, (a, b) => a.Id == b.Id).InnerJoin(q2, (a, b, c) => a.Name == c.Name).Select((a, b, c) => new { A = a, B = b, C = c }).ToList();
ret = q.InnerJoin(q1, (a, b) => a.Id == b.Id).LeftJoin(q2, (a, b, c) => a.Name == c.Name).RightJoin(q, (a, b, c, d) => a.Id == d.Id + 1).Select((a, b, c, d) => new { A = a, B = b, C = c, D = d }).ToList();
ret = q.InnerJoin(q1, (a, b) => a.Id == b.Id).InnerJoin(q2, (a, b, c) => a.Name == c.Name).RightJoin(q, (a, b, c, d) => a.Id == d.Id + 1).Select((a, b, c, d) => new { A = a, D = d }).ToList();
ret = q.InnerJoin(q1, (a, b) => a.Id == b.Id).InnerJoin(q2.Where(a => a.Id > 0).Select(a => a.Id), (a, b, c) => a.Id == c).RightJoin(q, (a, b, c, d) => a.Id == d.Id + 1).Select((a, b, c, d) => new { a, C = (int?)c }).ToList();
ret = q.InnerJoin(q1, (a, b) => a.Id == b.Id).LeftJoin(q2.Where(a => a.Id > 0).Select(a => a.Id), (a, b, c) => a.Id == c).FullJoin(q, (a, b, c, d) => a.Id == d.Id + 1).Select((a, b, c, d) => new { a, C = (int?)c }).ToList();
q.InnerJoin(q1, (a, b) => a.Id == b.Id).Select((a, b) => new { a, b }).Where(a => a.a.Id > 0).ToList();
Console.WriteLine(1);
}
public static void MappingTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
IQuery<User> q = context.Query<User>();
string n = "";
Expression<Func<User, bool>> p = a => a.Name == n;
//q.Where(a => a.Name == n);
var r = q.ToList();
Console.WriteLine(1);
}
public static void MethodTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
var q = context.Query<User>();
var space = new char[] { ' ' };
DateTime startTime = DateTime.Now;
DateTime endTime = DateTime.Now.AddDays(1);
var xxxx = q.Select(a => new
{
Id = a.Id,
String_Length = a.Name.Length,
Substring = a.Name.Substring(0),
Substring1 = a.Name.Substring(1),
Substring1_2 = a.Name.Substring(1, 2),
ToLower = a.Name.ToLower(),
ToUpper = a.Name.ToUpper(),
IsNullOrEmpty = string.IsNullOrEmpty(a.Name),
Contains = (bool?)a.Name.Contains("s"),
Trim = a.Name.Trim(),
TrimStart = a.Name.TrimStart(space),
TrimEnd = a.Name.TrimEnd(space),
StartsWith = (bool?)a.Name.StartsWith("s"),
EndsWith = (bool?)a.Name.EndsWith("s"),
SubtractTotalDays = endTime.Subtract(startTime).TotalDays,
SubtractTotalHours = endTime.Subtract(startTime).TotalHours,
SubtractTotalMinutes = endTime.Subtract(startTime).TotalMinutes,
SubtractTotalSeconds = endTime.Subtract(startTime).TotalSeconds,
SubtractTotalMilliseconds = endTime.Subtract(startTime).TotalMilliseconds,
Now = DateTime.Now,
UtcNow = DateTime.UtcNow,
Today = DateTime.Today,
Date = DateTime.Now.Date,
Year = DateTime.Now.Year,
Month = DateTime.Now.Month,
Day = DateTime.Now.Day,
Hour = DateTime.Now.Hour,
Minute = DateTime.Now.Minute,
Second = DateTime.Now.Second,
Millisecond = DateTime.Now.Millisecond,
Int_Parse = int.Parse("1"),
Int16_Parse = Int16.Parse("11"),
Long_Parse = long.Parse("2"),
Double_Parse = double.Parse("3"),
Float_Parse = float.Parse("4"),
Decimal_Parse = decimal.Parse("5"),
Guid_Parse = Guid.Parse("D544BC4C-739E-4CD3-A3D3-7BF803FCE179"),
Bool_Parse = bool.Parse("1"),
DateTime_Parse = DateTime.Parse("2014-1-1"),
B = a.Age == null ? false : a.Age > 1,
}).ToList();
ConsoleHelper.WriteLineAndReadKey();
}
public static void PredicateTest()
{
object ret = null;
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
var q = context.Query<User>();
List<int> ids = new List<int>();
ids.Add(1);
ids.Add(2);
ids.Add(2);
string name = "lu";
string nullString = null;
//name = null;
bool b = false;
bool b1 = true;
ret = q.Where(a => true).ToList();
ret = q.Where(a => a.Id == FeatureTest.ID).ToList();
ret = q.Where(a => a.Id == FeatureTest.ID || a.Id > 1).ToList();
ret = q.Where(a => a.Id == 1 && a.Name == name && a.Name == null && a.Name == nullString && a.Id == FeatureTest.ID).ToList();
ret = q.Where(a => ids.Contains(a.Id)).ToList();
ret = q.Where(a => !b == (a.Id > 0)).ToList();
ret = q.Where(a => a.Id > 0).Where(a => a.Id == 1).ToList();
ret = q.Where(a => !(a.Id > 10)).ToList();
ret = q.Where(a => !(a.Name == name)).ToList();
ret = q.Where(a => a.Name != name).ToList();
ret = q.Where(a => a.Name == name).ToList();
ret = q.Where(a => (a.Name == name) == (a.Id > 0)).ToList();
ret = q.Where(a => a.Name == (a.Name ?? name)).ToList();
ret = q.Where(a => (a.Age == null ? 0 : 1) == 1).ToList();
ret = q.Select(a => b & b1).ToList();
ret = q.Select(a => a.Id & 1).ToList();
ret = q.Select(a => new { Id = a.Id, And = a.Id & 1, And1 = a.Id & 1 & a.Id, Or = a.Id | 1, B = b & b1 }).ToList();
var xxx = q.ToList().Select(a => new { Id = a.Id, And = a.Id & 1, And1 = a.Id & 1 & a.Id, Or = a.Id | 1 }).ToList();
ret = q.Where(a => b & true).ToList();
ret = q.Where(a => b | true).ToList();
ret = q.Where(a => b || true).ToList();
ret = q.Where(a => b & b1).ToList();
ret = q.Where(a => b | b1).ToList();
ret = q.Where(a => b || b1).ToList();
Console.WriteLine(1);
Console.ReadKey();
}
public static void GroupQueryTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
object ret = null;
var q = context.Query<User>();
var r = q.GroupBy(a => a.Id).Having(a => a.Id > 1).Select(a => new { a.Id, Count = DbFunctions.Count(), Sum = DbFunctions.Sum(a.Id), Max = DbFunctions.Max(a.Id), Min = DbFunctions.Min(a.Id), Avg = DbFunctions.Average(a.Id) }).ToList();
q.GroupBy(a => a.Age).Having(a => a.Age > 1).Select(a => new { a.Age, Count = DbFunctions.Count(), Sum = DbFunctions.Sum(a.Age), Max = DbFunctions.Max(a.Age), Min = DbFunctions.Min(a.Age), Avg = DbFunctions.Average(a.Age) }).ToList();
var r1 = q.GroupBy(a => a.Age).Having(a => DbFunctions.Count() > 0).Select(a => new { a.Age, Count = DbFunctions.Count(), Sum = DbFunctions.Sum(a.Age), Max = DbFunctions.Max(a.Age), Min = DbFunctions.Min(a.Age), Avg = DbFunctions.Average(a.Age) }).ToList();
var g = q.GroupBy(a => a.Gender);
//g = g.ThenBy(a => a.Name);
//g = g.Having(a => a.Id > 0);
//g = g.Having(a => a.Name.Length > 0);
var gq = g.Select(a => new { Count = DbFunctions.Count() });
//gq = gq.Skip(1);
//gq = gq.Take(100);
//gq = gq.Where(a => a > -1);
ret = gq.ToList();
var c = gq.Count();
ConsoleHelper.WriteLineAndReadKey();
}
public static void TrackingTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
object ret = null;
var q = context.Query<User>();
q = q.AsTracking();
User user = q.First();
ret = context.Update(user);
Console.WriteLine(ret);
context.TrackEntity(user);
user.Name = user.Name + "1";
user.Age = user.Age;
user.Gender = null;
ret = context.Update(user);
Console.WriteLine(ret);
ret = context.Update(user);
Console.WriteLine(ret);
ConsoleHelper.WriteLineAndReadKey();
}
//[TestMethod]
public static void QueryTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
var q = context.Query<User>();
object ret = null;
q.Where(a => a.Id > 0).FirstOrDefault();
ret = q.Where(a => a.Id > 0).ToList();
ret = q.Where(a => a.Id > 0).OrderBy(a => a.Id).ToList();
ret = q.Where(a => a.Id > 0).OrderBy(a => a.Id).ThenByDesc(a => a.Age).ToList();
ret = q.Where(a => a.Id > 0).Skip(1).ToList();
ret = q.Where(a => a.Id > 0).Take(999).ToList();
ret = q.Where(a => a.Id > 0).Take(999).OrderBy(a => a.Age).ToList();
ret = q.Where(a => a.Id > 0).Skip(1).Take(999).ToList();
ret = q.Where(a => a.Id > 0).OrderBy(a => a.Id).ThenByDesc(a => a.Age).Skip(1).Take(999).ToList();
ret = q.Where(a => a.Id > 0).OrderBy(a => a.Id).ThenByDesc(a => a.Age).Skip(1).Take(999).Where(a => a.Id > -100).ToList();
ret = q.Select(a => new { Name1 = a.Name, Name2 = a.Name }).ToList();
Console.WriteLine(1);
Console.ReadKey();
}