使用数据集
/// <summary>
/// 库房信息类
/// </summary>
public class Kfxx
{
/// <summary>
/// 库房代码
/// </summary>
public string kfdm { get; set; }
/// <summary>
/// 库房名称
/// </summary>
public string kfmc { get; set; }
/// <summary>
/// 使用状态：0有效 1无效
/// </summary>
public int jlzt { get; set; }
}
/// <summary>
/// 帐类信息类
/// </summary>
public class Zlxx
{
/// <summary>
/// 库房代码
/// </summary>
public string kfdm { get; set; }
/// <summary>
/// 帐类代码
/// </summary>
public string zldm { get; set; }
/// <summary>
/// 帐类名称
/// </summary>
public string zlmc { get; set; }
/// <summary>
/// 金额
/// </summary>
public decimal je { get; set; }
}
/// <summary>
/// 统计期信息类
/// </summary>
public class Tjqxx
{
/// <summary>
/// 库房代码
/// </summary>
public string kfdm { get; set; }
/// <summary>
/// 帐类代码
/// </summary>
public string zldm { get; set; }
/// <summary>
/// 统计期
/// </summary>
public string ny { get; set; }
/// <summary>
/// 开始日期
/// </summary>
public DateTime ksrq { get; set; }
/// <summary>
/// 结束日期
/// </summary>
public DateTime jsrq { get; set; }
/// <summary>
/// 记账标志 0未记账 1已记账
/// </summary>
public int jzbz { get; set; }
/// <summary>
/// 金额
/// </summary>
public decimal je { get; set; }
} /// <summary>
/// 数据定义
/// </summary>
public class DataSource
{
public static List<Kfxx> kfxx { get; private set; }
public static List<Kfxx> kfxx2 { get; private set; }
public static List<Zlxx> zlxx { get; private set; }
public static List<Tjqxx> tjqxx { get; private set; }
static DataSource()
{
kfxx = new List<Kfxx>
{
new Kfxx{kfdm="",kfmc="总务科",jlzt=},
new Kfxx{kfdm="",kfmc="设备科",jlzt=},
new Kfxx{kfdm="",kfmc="供应室",jlzt=},
new Kfxx{kfdm="",kfmc="总务科",jlzt=}
};
kfxx2 = new List<Kfxx>
{
new Kfxx{kfdm="",kfmc="Union库房",jlzt=}
};
zlxx = new List<Zlxx>
{
new Zlxx{kfdm="",zldm="",zlmc="医用材料",je=},
new Zlxx{kfdm="",zldm="",zlmc="一次性材料",je=},
new Zlxx{kfdm="",zldm="",zlmc="器械材料",je=},
new Zlxx{kfdm="",zldm="",zlmc="无统计期",je=}
};
tjqxx = new List<Tjqxx>
{
new Tjqxx{kfdm="",zldm="",ny="",ksrq=Convert.ToDateTime("2013-06-01 00:00:00"),jsrq=Convert.ToDateTime("2013-06-30 23:59:59"),jzbz=,je=},
new Tjqxx{kfdm="",zldm="",ny="",ksrq=Convert.ToDateTime("2013-07-01 00:00:00"),jsrq=Convert.ToDateTime("2013-07-31 23:59:59"),jzbz=,je=},
new Tjqxx{kfdm="",zldm="",ny="",ksrq=Convert.ToDateTime("2013-07-01 00:00:00"),jsrq=Convert.ToDateTime("2013-07-31 23:59:59"),jzbz=,je=},
new Tjqxx{kfdm="",zldm="",ny="",ksrq=Convert.ToDateTime("2013-07-01 00:00:00"),jsrq=Convert.ToDateTime("2013-07-31 23:59:59"),jzbz=,je=}
};
}
}
数据集
1.简单查询
var Query = from a in DataSource.kfxx
where a.kfdm==""
select new
{
a.kfdm,
a.kfmc
};
var Query = DataSource.kfxx.Select(p => new { p.kfdm, p.kfmc }).Where(p => p.kfdm == "");2.distinct,first,last,skip,take,single
var Query = DataSource.kfxx.Select(p => new { p.kfdm, p.kfmc }).Distinct();
var Query = DataSource.kfxx.Select(p => new { p.kfdm, p.kfmc }).First();
var Query = DataSource.kfxx.Select(p => new { p.kfdm, p.kfmc }).Last();
var Query = DataSource.kfxx.Select(p => new { p.kfdm, p.kfmc }).Skip();
var Query = DataSource.kfxx.Select(p => new { p.kfdm, p.kfmc }).Take();
var Query = DataSource.kfxx.Where(p => p.kfdm == "").Select(p => new { p.kfdm, p.kfmc }).Single();扩展方法
3.排序
var Query = from a in DataSource.kfxx
orderby a.kfdm descending, a.jlzt
select new
{
a.kfdm,
a.jlzt
};
var Query = DataSource.kfxx.OrderByDescending(p => p.kfdm).ThenBy(p => p.jlzt).Select(p => new { p.kfdm, p.jlzt });4.Join
4.1 inner join
var Query = from a in DataSource.kfxx
from b in DataSource.zlxx
where a.jlzt == && a.kfdm==b.kfdm
orderby a.kfdm descending, a.jlzt
select new
{
a.kfdm,
a.kfmc,
b.zldm,
b.zlmc
};
var Query = (DataSource.zlxx.Join(DataSource.kfxx.Where(q => q.jlzt == ),
p => p.kfdm, q => q.kfdm,
(p, q) => new { q.kfdm, q.kfmc, p.zldm, p.zlmc, q.jlzt }))
.OrderByDescending(s => s.kfdm).ThenBy(s => s.jlzt)
.Select(t => new { t.kfdm, t.kfmc, t.zldm, t.zlmc });
4.2 left join
var Query = from a in DataSource.kfxx
from b in DataSource.zlxx
join c in DataSource.tjqxx on new { kfdm = b.kfdm, zldm = b.zldm } equals new { kfdm = c.kfdm, zldm = c.zldm }
into gg
from g in gg.DefaultIfEmpty()
where a.jlzt == && a.kfdm == b.kfdm && (g == null ? : g.jzbz) ==
//orderby a.kfdm descending, a.jlzt
select new
{
a.kfdm,
a.kfmc,
b.zldm,
b.zlmc,
ny = g == null ? "" : g.ny,
ksrq = g == null ? "" : g.ksrq.ToString(),
jsrq = g == null ? "" : g.jsrq.ToString()
};
var Query = (DataSource.zlxx.Join(DataSource.kfxx.Where(q => q.jlzt == ),
p => p.kfdm, q => q.kfdm,
(p, q) => new { q.kfdm, q.kfmc, p.zldm, p.zlmc }))
.GroupJoin(DataSource.tjqxx.Where(tjq=>tjq.jzbz==), zl => new { zl.kfdm, zl.zldm }, tjq => new { tjq.kfdm, tjq.zldm },
(zl, tjq) => new
{
zl = zl,
tjq = tjq
})
.SelectMany(group => group.tjq.DefaultIfEmpty(),
(group, tjq) => new
{
group.zl.kfdm,
group.zl.kfmc,
group.zl.zldm,
group.zl.zlmc,
ny = tjq == null ? "" : tjq.ny,
ksrq = tjq == null ? "" : tjq.ksrq.ToString(),
jsrq = tjq == null ? "" : tjq.jsrq.ToString()
}
);
5.聚合(count,sum,max,min,avg)
var Query = from a in DataSource.kfxx
join b in DataSource.zlxx on a.kfdm equals b.kfdm
into gg
where a.jlzt ==
from g in gg.DefaultIfEmpty()
group g by a.kfdm into gg1
where gg1.Sum(p => p == null ? : p.je)>
select new
{
kfdm = gg1.Key,
zlcount = gg1.Count(p => (p == null ? "" : p.zldm) != ""),
zje = gg1.Sum(p => p == null ? : p.je),
je_min = gg1.Min(p => p == null ? : p.je),
je_max = gg1.Max(p => p == null ? : p.je),
je_avg = gg1.Average(p => p == null ? : p.je)
};
var Query = DataSource.kfxx.Where(g => g.jlzt == ).GroupJoin(DataSource.zlxx, kf => kf.kfdm, zl => zl.kfdm,
(kf, zl) => new
{
kfdm = kf.kfdm,
zlcount = zl.DefaultIfEmpty().Count(p => (p == null ? "" : p.zldm) != ""),
zje = zl.DefaultIfEmpty().Sum(p => p == null ? : p.je),
je_min = zl.DefaultIfEmpty().Min(p => p == null ? : p.je),
je_max = zl.DefaultIfEmpty().Max(p => p == null ? : p.je),
je_avg = zl.DefaultIfEmpty().Average(p => p == null ? : p.je)
}).Where(g=>g.zje>);
6.其他
6.1 Union
var Query = DataSource.kfxx.Union(DataSource.kfxx2);
6.2 自定义聚合Aggregate
//自定义求平均为例
var Query = DataSource.kfxx.Where(g => g.jlzt == ).GroupJoin(DataSource.zlxx, kf => kf.kfdm, zl => zl.kfdm,
(kf, zl) => new
{
kfdm = kf.kfdm,
je_avg0 = zl.DefaultIfEmpty().Average(p => p == null ? : p.je),
je_avg1 = zl.DefaultIfEmpty().Aggregate(new Zlxx {je=},
(a, b) => { return new Zlxx { je = (a == null ? : a.je) + (b == null ? : b.je) }; },
c => new Zlxx { je = (c == null ? : c.je )/ (zl.DefaultIfEmpty().Count(p => (p == null ? "" : p.zldm) != "") == ? : (zl.DefaultIfEmpty().Count(p => (p == null ? "" : p.zldm) != ""))) }).je.ToString("C")
});
源代码(http://download.csdn.net/detail/lk13962517093/5692997)
小弟刚学，不正确之处请指点。