LinqTestDBDataContext a = newLinqTestDBDataContext(); a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True"; System.Data.Linq.Table<DBItem> t = a.DBItem; foreach (var temp in t) { Console.WriteLine("{0},{1}", temp.ItemID, temp.ItemMatter); }
Lambda
LinqTestDBDataContext a = newLinqTestDBDataContext(); a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True"; System.Linq.IQueryable<DBItem> t = a.DBItem.Where (p=>p.ItemID=="a"); foreach (var temp in t) { Console.WriteLine("{0},{1}", temp.ItemID, temp.ItemMatter); }
Linq
LinqTestDBDataContext a = newLinqTestDBDataContext(); a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True"; var t = from temp in a.DBItem where temp.ItemID == "a"select temp; foreach (var temp in t) { Console.WriteLine("{0},{1}", temp.ItemID, temp.ItemMatter); }
查询并返回新组合(投影)
LinqTestDBDataContext a = newLinqTestDBDataContext(); a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";
var t = from temp in a.DBItem selectnew { temp.ItemID, temp.ItemMatter, bak = "wxd" }; //System.Linq.IQueryable tt = a.DBItem.Select(p => new { p.ItemID, p.ItemMatter, bak = "wxd" });
LinqTestDBDataContext a = newLinqTestDBDataContext(); a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";
var r = from temp1 in a.DBPerson join temp2 in a.DBPersonExtension on temp1.NameID equals temp2.NameID selectnew { temp1.NameID ,temp1.Name,temp2.Age,temp2.Sex,temp2.Login};
LinqTestDBDataContext a = newLinqTestDBDataContext(); a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";
int BeginN = 8; int N = 5; var r = a.DBItemList.Skip(BeginN).Take(N);
this.dataGridView1.DataSource = r;
分组
LinqTestDBDataContext a = newLinqTestDBDataContext(); a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";
var r = from temp in a.DBItemList group temp by temp.ItemID into gp select gp;
List<DBItemList> l = newList<DBItemList>(); foreach (var s in r) { Console.WriteLine("{0}组", s.Key); ; foreach (var ss in s) { Console.WriteLine("{0},{1},{2}",ss.ItemID,ss.NameID,ss.ItemValue);
LinqTestDBDataContext a = newLinqTestDBDataContext(); a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";
var x = from tp1 in a.DBItemList.Select(p => new { p.ItemID }).Distinct() join tp2 in a.DBItemList on tp1.ItemID equals tp2.ItemID into newtab selectnew { tp1.ItemID, ValueSum = newtab.Sum(p => p.ItemValue) };
this.dataGridView1.DataSource = x;
与该效果相同
select ItemID ,sum(ItemValue)as ValueSum from dbo.DBItemList groupby ItemID
foreach (var tp in r) //执行LINQ { System.Console.WriteLine(tp); }
多个LINQ查询会合并成一个SQL提交
LinqTestDBDataContext a = newLinqTestDBDataContext(); a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";
var x =from tp in a.DBItemList where tp.NameID=="n01" select tp;
var y = x.Where(p => p.ItemID == "a");
this.dataGridView1.DataSource =y.Take(5);
事件跟踪器
execsp_executesql N'SELECT TOP 5 [t0].[AutoId], [t0].[NameID], [t0].[ItemID], [t0].[ItemValue] FROM [dbo].[DBItemList] AS [t0] WHERE ([t0].[ItemID] = @p0) AND ([t0].[NameID] = @p1)',N'@p0 nvarchar(1),@p1 nvarchar(3)',@p0=N'a',@p1=N'n01'
自动缓存
查询后数据会被缓存,如果之后以主键查询的话,会先在缓存里查找
命中例子
LinqTestDBDataContext a = newLinqTestDBDataContext("Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True");
execsp_executesql N'SELECT [t0].[AutoId], [t0].[NameID], [t0].[ItemID], [t0].[ItemValue] FROM [dbo].[DBItemList] AS [t0] WHERE [t0].[ItemID] = @p0',N'@p0 nvarchar(1)',@p0=N'a'
没命中例子
LinqTestDBDataContext a = newLinqTestDBDataContext("Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True");
execsp_executesql N'SELECT [t0].[AutoId], [t0].[NameID], [t0].[ItemID], [t0].[ItemValue] FROM [dbo].[DBItemList] AS [t0] WHERE [t0].[ItemID] = @p0',N'@p0 nvarchar(1)',@p0=N'a'
execsp_executesql N'SELECT [t0].[AutoId], [t0].[NameID], [t0].[ItemID], [t0].[ItemValue] FROM [dbo].[DBItemList] AS [t0] WHERE [t0].[AutoId] = @p0',N'@p0 int',@p0=7
GetCommand方法 (得到linq 查询对应的SQL语句)
LinqTestDBDataContext a = newLinqTestDBDataContext(); a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";
LinqTestDBDataContext a = newLinqTestDBDataContext(); a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";
var r = a.DBItem;
foreach (var s in r) { // 每一次迭代都会执行一条SQL语句来返回当前DBItem的DBItemList foreach (var ss in s.DBItemList) { System.Console.WriteLine("{0},{1},{2}", ss.ItemID, ss.NameID, ss.ItemValue); }
SELECT [t0].[ItemID], [t0].[ItemMatter] FROM [dbo].[DBItem] AS [t0]
execsp_executesql N'SELECT [t0].[AutoId], [t0].[NameID], [t0].[ItemID], [t0].[ItemValue] FROM [dbo].[DBItemList] AS [t0] WHERE [t0].[ItemID] = @p0',N'@p0 nvarchar(1)',@p0=N'a'
execsp_executesql N'SELECT [t0].[AutoId], [t0].[NameID], [t0].[ItemID], [t0].[ItemValue] FROM [dbo].[DBItemList] AS [t0] WHERE [t0].[ItemID] = @p0',N'@p0 nvarchar(1)',@p0=N'b'
execsp_executesql N'SELECT [t0].[AutoId], [t0].[NameID], [t0].[ItemID], [t0].[ItemValue] FROM [dbo].[DBItemList] AS [t0] WHERE [t0].[ItemID] = @p0',N'@p0 nvarchar(1)',@p0=N'c'
使用数据加载选项
LinqTestDBDataContext a = newLinqTestDBDataContext(); a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";
System.Data.Linq.DataLoadOptions options = new System.Data.Linq.DataLoadOptions();
SELECT [t0].[ItemID], [t0].[ItemMatter], [t1].[AutoId], [t1].[NameID], [t1].[ItemID] AS [ItemID2], [t1].[ItemValue],( SELECTCOUNT(*) FROM [dbo].[DBItemList] AS [t2] WHERE [t2].[ItemID] = [t0].[ItemID] )AS [count] FROM [dbo].[DBItem] AS [t0] LEFTOUTERJOIN [dbo].[DBItemList] AS [t1] ON [t1].[ItemID] = [t0].[ItemID] ORDERBY [t0].[ItemID], [t1].[AutoId]
使用数据加载选项加载数据,并且限制所要加载的数据
LinqTestDBDataContext a = newLinqTestDBDataContext(); a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";
System.Data.Linq.DataLoadOptions options = new System.Data.Linq.DataLoadOptions(); options.LoadWith<DBItem>(p => p.DBItemList);
execsp_executesql N'SELECT [t0].[ItemID], [t0].[ItemMatter], [t1].[AutoId], [t1].[NameID], [t1].[ItemID] AS [ItemID2], [t1].[ItemValue], ( SELECT COUNT(*) FROM [dbo].[DBItemList] AS [t2] WHERE ([t2].[NameID] = @p0) AND ([t2].[ItemID] = [t0].[ItemID]) ) AS [count] FROM [dbo].[DBItem] AS [t0] LEFT OUTER JOIN [dbo].[DBItemList] AS [t1] ON ([t1].[NameID] = @p0) AND ([t1].[ItemID] = [t0].[ItemID]) ORDER BY [t0].[ItemID], [t1].[AutoId]',N'@p0 nvarchar(3)',@p0=N'n01'
System.Data.Linq.SqlClient.SqlMethods
Like 静态方法
LinqTestDBDataContext a = newLinqTestDBDataContext("Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True");
var li = a.DBItem .Where(p => System.Data.Linq.SqlClient.SqlMethods.Like(p.ItemMatter, "this is%")); dataGridView1.DataSource =li;