LINQ to DB is the fastest LINQ database access library offering a simple, light, fast, and type-safe layer between your POCO objects and your database.
Architecturally it is one step above micro-ORMs like Dapper, Massive, or PetaPoco, in that you work with LINQ expressions, not with magic strings, while maintaining a thin abstraction layer between your code and the database. Your queries are checked by the C# compiler and allow for easy refactoring.
However, it's not as heavy as LINQ to SQL or Entity Framework. There is no change-tracking, so you have to manage that yourself, but on the positive side you get more control and faster access to your data.
In other words LINQ to DB is type-safe SQL.
linq2db is a .NET Foundation project.
Development version nuget feed (how to use)
Visit our blog and see Github.io documentation for more details.
Code examples and demos can be found here or in tests.
Release Notes page.
Notable open-source users:
Unmantained projects:
No, this is not the donate link. We do need something really more valuable - your time. If you really want to help us please read this post.
From NuGet:
Install-Package linq2db
You can simply pass provider name and connection string into DataConnection
constructor:
var db = new LinqToDB.Data.DataConnection(
LinqToDB.ProviderName.SqlServer2012,
"Server=.\;Database=Northwind;Trusted_Connection=True;Enlist=False;");
You can configure connection options from code using LinqToDbConnectionOptionsBuilder
class (check class for available options):
// create options builder
var builder = new LinqToDbConnectionOptionsBuilder();
// configure connection string
builder.UseSqlServer(connectionString);
// or using custom connection factory
b.UseConnectionFactory(
SqlServerTools.GetDataProvider(
SqlServerVersion.v2017,
SqlServerProvider.MicrosoftDataSqlClient),
() =>
{
var cn = new SqlConnection(connectionString);
cn.AccessToken = accessToken;
return cn;
});
// pass configured options to data connection constructor
var dc = new DataConnection(builder.Build());
In your web.config
or app.config
make sure you have a connection string (check this file for supported providers):
<connectionStrings>
<add name="Northwind"
connectionString = "Server=.\;Database=Northwind;Trusted_Connection=True;Enlist=False;"
providerName = "SqlServer" />
</connectionStrings>
.Net Core does not support System.Configuration
until 3.0 so to configure connection strings you should implement ILinqToDBSettings
, for example:
public class ConnectionStringSettings : IConnectionStringSettings
{
public string ConnectionString { get; set; }
public string Name { get; set; }
public string ProviderName { get; set; }
public bool IsGlobal => false;
}
public class MySettings : ILinqToDBSettings
{
public IEnumerable<IDataProviderSettings> DataProviders => Enumerable.Empty<IDataProviderSettings>();
public string DefaultConfiguration => "SqlServer";
public string DefaultDataProvider => "SqlServer";
public IEnumerable<IConnectionStringSettings> ConnectionStrings
{
get
{
yield return
new ConnectionStringSettings
{
Name = "Northwind",
ProviderName = "SqlServer",
ConnectionString = @"Server=.\;Database=Northwind;Trusted_Connection=True;Enlist=False;"
};
}
}
}
And later just set on program startup before the first query is done (Startup.cs for example):
DataConnection.DefaultSettings = new MySettings();
See article.
You can generate POCO classes from your database using T4 templates. These classes will be generated using the Attribute configuration
. Demonstration video could be found here.
Alternatively, you can write them manually, using Attribute configuration
, Fluent configuration
, or inferring.
using System;
using LinqToDB.Mapping;
[Table(Name = "Products")]
public class Product
{
[PrimaryKey, Identity]
public int ProductID { get; set; }
[Column(Name = "ProductName"), NotNull]
public string Name { get; set; }
[Column]
public int VendorID { get; set; }
[Association(ThisKey = nameof(VendorID), OtherKey=nameof(Vendor.ID))]
public Vendor Vendor { get; set; }
// ... other columns ...
}
This approach involves attributes on all properties that should be mapped. This way lets you to configure all possible things linq2db ever supports. There one thing to mention: if you add at least one attribute into POCO, all other properties should also have attributes, otherwise they will be ignored:
using System;
using LinqToDB.Mapping;
[Table(Name = "Products")]
public class Product
{
[PrimaryKey, Identity]
public int ProductID { get; set; }
public string Name { get; set; }
}
Property Name
will be ignored as it lacks Column
attibute.
This method lets you configure your mapping dynamically at runtime. Furthermore, it lets you to have several different configurations if you need so. You will get all configuration abilities available with attribute configuration. These two approaches are interchangeable in its abilities. This kind of configuration is done through the class MappingSchema
.
With Fluent approach you can configure only things that require it explicitly. All other properties will be inferred by linq2db:
var builder = MappingSchema.Default.GetFluentMappingBuilder();
builder.Entity<Product>()
.HasTableName("Products")
.HasSchemaName("dbo")
.HasIdentity(x => x.ProductID)
.HasPrimaryKey(x => x.ProductID)
.Ignore(x => x.SomeNonDbProperty)
.Property(x => x.TimeStamp)
.HasSkipOnInsert()
.HasSkipOnUpdate()
.Association(x => x.Vendor, x => x.VendorID, x => x.VendorID, canBeNull: false)
;
//... other mapping configurations
In this example we configured only three properties and one association. We let linq2db to infer all other properties which have to match with column names. However, other associations will not get configured automatically.
There is a static property LinqToDB.Mapping.MappingSchema.Default
which may be used to define a global configuration. This mapping is used by default if no mapping schema provided explicitly. The other way is to pass instance of MappingSchema
into constructor alongside with connection string.
This approach involves no attributes at all. In this case linq2db will use POCO's name as table name and property names as column names (with exact same casing, which could be important for case-sensitive databases). This might seem to be convenient, but there are some restrictions: linq2db will not infer primary key even if class has property called "ID"; it will not infer nullability of string properties as there is no way to do so; and associations will not be automatically configured.
using System;
using LinqToDB.Mapping;
public class Product
{
public int ProductID { get; set; }
public string Name { get; set; }
public int VendorID { get; set; }
public Vendor Vendor { get; set; }
// ... other columns ...
}
This way linq2db will auto-configure Product
class to map to Product
table with fields ProductID
, Name
, and VendorID
. POCO will not get ProductID
property treated as primary key. And there will be no association with Vendor
.
This approach is not generally recommended.
At this point LINQ to DB doesn't know how to connect to our database or which POCOs go with what database. All this mapping is done through a DataConnection
class:
public class DbNorthwind : LinqToDB.Data.DataConnection
{
public DbNorthwind() : base("Northwind") { }
public ITable<Product> Product => GetTable<Product>();
public ITable<Category> Category => GetTable<Category>();
// ... other tables ...
}
We call the base constructor with the "Northwind" parameter. This parameter (called configuration name
) has to match the name="Northwind" we defined above in our connection string. We also have to register our Product
class we defined above to allow us to write LINQ queries.
And now let's get some data:
using LinqToDB;
using LinqToDB.Common;
public static List<Product> All()
{
using (var db = new DbNorthwind())
{
var query = from p in db.Product
where p.ProductID > 25
orderby p.Name descending
select p;
return query.ToList();
}
}
Make sure you always wrap your DataConnection
class (in our case DbNorthwind
) in a using
statement. This is required for proper resource management, like releasing the database connections back into the pool. More details
Most times we get the entire row from the database:
from p in db.Product
where p.ProductID == 5
select p;
However, sometimes getting all the fields is too wasteful so we want only certain fields, but still use our POCOs; something that is challenging for libraries that rely on object tracking, like LINQ to SQL.
from p in db.Product
orderby p.Name descending
select new Product
{
Name = p.Name
};
Rather than concatenating strings we can 'compose' LINQ expressions. In the example below the final SQL will be different if onlyActive
is true or false, or if searchFor
is not null.
public static List<Product> All(bool onlyActive, string searchFor)
{
using (var db = new DbNorthwind())
{
var products = from p in db.Product
select p;
if (onlyActive)
{
products = from p in products
where !p.Discontinued
select p;
}
if (searchFor != null)
{
products = from p in products
where p.Name.Contains(searchFor)
select p;
}
return products.ToList();
}
}
A lot of times we need to write code that returns only a subset of the entire dataset. We expand on the previous example to show what a product search function could look like.
Keep in mind that the code below will query the database twice. Once to find out the total number of records, something that is required by many paging controls, and once to return the actual data.
public static List<Product> Search(string searchFor, int currentPage, int pageSize, out int totalRecords)
{
using (var db = new DbNorthwind())
{
var products = from p in db.Product
select p;
if (searchFor != null)
{
products = from p in products
where p.Name.Contains(searchFor)
select p;
}
totalRecords = products.Count();
return products.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
}
}
This assumes we added a Category
class, just like we did with the Product
class, defined all the fields, and registered it in our DbNorthwind
data access class. We can now write an INNER JOIN query like this:
from p in db.Product
join c in db.Category on p.CategoryID equals c.CategoryID
select new Product
{
Name = p.Name,
Category = c
};
and a LEFT JOIN query like this:
from p in db.Product
from c in db.Category.Where(q => q.CategoryID == p.CategoryID).DefaultIfEmpty()
select new Product
{
Name = p.Name,
Category = c
};
In the previous example we assign an entire Category
object to our product, but what if we want all the fields in our Product
class, but we don't want to specify every field by hand? Unfortunately, we cannot write this:
from p in db.Product
from c in db.Category.Where(q => q.CategoryID == p.CategoryID).DefaultIfEmpty()
select new Product(c);
The query above assumes the Product class has a constructor that takes in a Category object. The query above won't work, but we can work around that with the following query:
from p in db.Product
from c in db.Category.Where(q => q.CategoryID == p.CategoryID).DefaultIfEmpty()
select Product.Build(p, c);
For this to work, we need a function in the Product
class that looks like this:
public static Product Build(Product product, Category category)
{
if (product != null)
{
product.Category = category;
}
return product;
}
One caveat with this approach is that if you're using it with composed queries (see example above) the select Build part has to come only in the final select.
At some point we will need to add a new Product
to the database. One way would be to call the Insert
extension method found in the LinqToDB
namespace; so make sure you import that.
using LinqToDB;
using (var db = new DbNorthwind())
{
db.Insert(product);
}
This inserts all the columns from our Product class, but without retrieving the generated identity value. To do that we can use InsertWith*Identity
methods, like this:
using LinqToDB;
using (var db = new DbNorthwind())
{
product.ProductID = db.InsertWithInt32Identity(product);
}
There is also InsertOrReplace
that updates a database record if it was found by primary key or adds it otherwise.
If you need to insert only certain fields, or use values generated by the database, you could write:
using LinqToDB;
using (var db = new DbNorthwind())
{
db.Product
.Value(p => p.Name, product.Name)
.Value(p => p.UnitPrice, 10.2m)
.Value(p => p.Added, () => Sql.CurrentTimestamp)
.Insert();
}
Use of this method also allows us to build insert statements like this:
using LinqToDB;
using (var db = new DbNorthwind())
{
var statement = db.Product
.Value(p => p.Name, product.Name)
.Value(p => p.UnitPrice, 10.2m);
if (storeAdded) statement.Value(p => p.Added, () => Sql.CurrentTimestamp);
statement.Insert();
}
Updating records follows similar pattern to Insert. We have an extension method that updates all the columns in the database:
using LinqToDB;
using (var db = new DbNorthwind())
{
db.Update(product);
}
And we also have a lower level update mechanism:
using LinqToDB;
using (var db = new DbNorthwind())
{
db.Product
.Where(p => p.ProductID == product.ProductID)
.Set(p => p.Name, product.Name)
.Set(p => p.UnitPrice, product.UnitPrice)
.Update();
}
Similarly, we can break an update query into multiple pieces if needed:
using LinqToDB;
using (var db = new DbNorthwind())
{
var statement = db.Product
.Where(p => p.ProductID == product.ProductID)
.Set(p => p.Name, product.Name);
if (updatePrice) statement = statement.Set(p => p.UnitPrice, product.UnitPrice);
statement.Update();
}
You're not limited to updating a single record. For example, we could discontinue all the products that are no longer in stock:
using LinqToDB;
using (var db = new DbNorthwind())
{
db.Product
.Where(p => p.UnitsInStock == 0)
.Set(p => p.Discontinued, true)
.Update();
}
Similar to how you update records, you can also delete records:
using LinqToDB;
using (var db = new DbNorthwind())
{
db.Product
.Where(p => p.Discontinued)
.Delete();
}
Bulk copy feature supports the transfer of large amounts of data into a table from another data source. For more details read this article.
using LinqToDB.Data;
[Table(Name = "ProductsTemp")]
public class ProductTemp
{
[PrimaryKey]
public int ProductID { get; set; }
[Column(Name = "ProductName"), NotNull]
public string Name { get; set; }
// ... other columns ...
}
var list = new List<ProductTemp>();
// populate list
using (var db = new DbNorthwind())
{
db.BulkCopy(list);
}
Using database transactions is easy. All you have to do is call BeginTransaction() on your DataConnection, run one or more queries, and then commit the changes by calling CommitTransaction(). If something happened and you need to roll back your changes you can either call RollbackTransaction() or throw an exception.
using (var db = new DbNorthwind())
{
db.BeginTransaction();
// ... select / insert / update / delete ...
if (somethingIsNotRight)
{
db.RollbackTransaction();
}
else
{
db.CommitTransaction();
}
}
Also, you can use .NET built-in TransactionScope class:
// don't forget that isolation level is serializable by default
using (var transaction = new TransactionScope())
// or for async code
// using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (var db = new DbNorthwind())
{
...
}
transaction.Complete();
}
It should be noted that there are two base classes for your "context" class: LinqToDB.Data.DataConnection
and LinqToDB.DataContext
. The key difference between them is in connection retention behaviour. DataConnection
opens connection with first query and holds it open until dispose happens. DataContext
behaves the way you might used to with Entity Framework: it opens connection per query and closes it right after query is done.
This difference in behavior matters when used with TransactionScope
:
using var db = new LinqToDB.Data.DataConnection("provider name", "connection string");
var product = db.GetTable<Product>()
.FirstOrDefault(); // connection opened here
var scope = new TransactionScope();
// this transaction was not attached to connection
// because it was opened earlier
product.Name = "Lollipop";
db.Update(product);
scope.Dispose();
// no transaction rollback happed, "Lollipop" has been saved
A DataConnection
is attached with ambient transaction in moment it is opened. Any TransactionScope
s created after the connection is created will no effect on that connection. Replacing DataConnection
with DataContext
in code shown earlier will make transaction scope work as expected: the created record will be discarded with the transaction.
Although, DataContext
appears to be the right class to choose, it is strongly recommended to use DataConnection
instead. It's default behaviour might be changed with setting CloseAfterUse
property to true
:
public class DbNorthwind : LinqToDB.Data.DataConnection
{
public DbNorthwind() : base("Northwind")
{
(this as IDataContext).CloseAfterUse = true;
}
}
Here you can read about MERGE support.
Here you can read about Window (Analytic) Functions support.
If you would like to use MiniProfiler or other profiling tool that wraps ADO.NET provider classes, you need to configure our regular DataConnection to use wrapped connection.
// example of SQL Server-backed data connection with MiniProfiler enabled for debug builds
public class DbDataContext : DataConnection
{
// let's use profiler only for debug builds
#if !DEBUG
public DbDataContext() : base("Northwind")
{
// this is important part:
// here we tell linq2db how to access underlying ADO.NET classes of used provider
// if you don't configure those mappings, linq2db will be unable to use provider-specific functionality
// which could lead to loss or unavailability of some functionality when profiled connection enabled
MappingSchema.SetConvertExpression<ProfiledDbConnection, IDbConnection> (db => db.WrappedConnection);
MappingSchema.SetConvertExpression<ProfiledDbDataReader, IDataReader> (db => db.WrappedReader);
MappingSchema.SetConvertExpression<ProfiledDbTransaction, IDbTransaction>(db => db.WrappedTransaction);
MappingSchema.SetConvertExpression<ProfiledDbCommand, IDbCommand> (db => db.InternalCommand);
}
#else
public DbDataContext() : base(GetDataProvider(), GetConnection()) { }
private static IDataProvider GetDataProvider()
{
// create provider instance (SQL Server 2012 provider in our case)
return new SqlServerDataProvider("", SqlServerVersion.v2012);
}
private static IDbConnection GetConnection()
{
// create provider-specific connection instance. SqlConnection in our case
var dbConnection = new SqlConnection(@"Server=.\SQL;Database=Northwind;Trusted_Connection=True;Enlist=False;");
// wrap it by profiler's connection implementation
return new StackExchange.Profiling.Data.ProfiledDbConnection(dbConnection, MiniProfiler.Current);
}
#endif
}
Still have questions left? Check out our documentation site and FAQ
using LinqToDB; using System; using System.Linq; namespace ConsoleApp1 { class Program { static void Main(string[] args) { using (var db = new TestDB()) { //Insert db.Insert(new UserTB() { Name = "张三"
【问题描述】 在对linq2db进行删除操作前,先将需要删除的数据集查询出来, 再调用db.[表对象].delete()方法执行删除操作,代码如下: using (SqliteDB db = new SqliteDB(connectionString)) { IQuerable<T> infoList = db.[表对象].where(x => x.attr == condition);
//添加数据 public static bool InsertSeries(string SeriesName, string SubSeriesName, string OPIName, string OPICode, string States, string InsideCode, int MinStock, int AddStockNum, string
[table]表一 Student { [PrimaryKey] Id, Name, ClassID 外键 -- [Association] | classId | } | | [table]表二 | Cl
方法为: public static object InsertWithIdentity<T>(this IDataContext dataContext, T obj); 例: var rowId = db.InsertWithIdentity(newRow); newRow.Id = (long)rowId; 对应的SQL语句为: SELECT last_insert_rowid()
C# Linq 左外联 假设我们有一个Student类,具有姓名、年龄、班级Id三个属性 还有一个Class类,具有Id,班级名两个属性 现要查找学生姓名、年龄和其所在班级名,因此需要做一个Student表左联Class表的操作 方式一: var results = from s in students join c in classes on s.ClassId eq
左外部联接 (相对于sql:left join | left outer join) 它返回第一个集合中的所有元素,无论它是否在第二个集合中有相关元素。在 LINQ 中,通过对分组联接的结果调用 DefaultIfEmpty()方法来执行左外部联接。DefaultIfEmpty()方法从列表中获取指定元素。如果列表为空,则返回默认值。 通俗来说,就是使用此方法,返回第一个集合中所有元素,假如第一个
Table:Employee 部门:Employee.DeptCode 上级部门:Employee.SuperiorDept 工号:Employee.Emplid Distinct的原因是递归出来的资料有重复。 [Fact] private void TestX() { var Query= GetEmpListData("
var qurry = from bind in repDb.DbQuery.Where(p => p.PrescriptionInfo.DecocteScheme.DecocteSchemeDetails.Select(q => q.DecocteType).Contains(DecoctMethod.BackDown)
打开SQL server目录下的 SQL server Profiler 点击菜单“文件”正下方第一个图标“新建跟踪”,然后根据指引操作,运行linq代码就可以看到啦
本文章转载:http://www.cnblogs.com/wangiqngpei557/archive/2013/02/05/2893096.html 参考:http://dotnet.9sssd.com/entfwk/art/960 http://www.cnblogs.com/killuakun/archive/2008/08/03/1259389.html http://www.cnblo
很多人做项目时候当喜欢用DateTime方法来做服务器时间直接写入了数据库,不得不说这是非常错误的,有些用户的电脑自己会把自己的电脑时间改了忘记了改回正确的时间,或者有用户的电脑很老很旧主板的电池已经坏了,每次开机时间都是主板出厂的默认时间,这个时候用DateTime只有给自己和团队添加麻烦,报表永远都不对,所以很有必要所有的时间统一使用服务器的时间。 如果是用ADO.NET就很方便,Sql直接用
前面介绍了Linq的三个方面应用:Linq to SQL, Linq to XML和Linq to Object,这篇介绍一下动态Linq的实现方式及应用场景。 命名空间: System.Linq; System.Linq.Expressions; 应用Linq的时候,我们都知道仅仅须要Lambda表达式即可,但有些场景仅仅仅仅使用Data Model的字段名操作是不够的或者不方便的。 场景1:如
异步操作是提高Web应用程序吞吐量的重要手段,关于这方面的话题已经在前文《正确使用异步操作》中解释过了。对于大多数互联网应用来说,性能瓶颈数据库访问。换句话说,一个请求在数据库操作上所花的时间往往是最多的——并且占总时间的90%以上。因此,当Web应用程序的吞吐量因为数据库操作的阻塞而受到影响的话,我们可是尝试使用异步数据库操作来进行优化。 如果我们使用LINQ to SQL,在默认情况下是无法实
转自: http://www.cnblogs.com/pato/archive/2011/03/04/1971003.html 这里介绍Linq使用Group By和Count得到每个CategoryID中产品的数量,Linq使用Group By和Count得到每个CategoryID中断货产品的数量等方面。 学经常会遇到Linq使用Group By问题,这里将介绍Linq使用Group By问题
首先解释一下Skip和Take都用来干嘛的。 1.Skip跳过指定数量的项,并获取剩余的项 2.Take提取制定数量的项 3.TakeWhile根据指定条件提出数据 4.SkipWhile根据指定条件跳过数据 实例如下: /// <summary> /// Take用法 /// </summary> public void Linq1() { int[] numbers = { 5, 4,
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; using System.
有一个班级表,主键是class_id,在管理班级时要进行逻辑删除,而只是单纯的is_del字段(记录每条数据是否有效)更改为true,主键class_id如果不变动,在再次增加一个班级时,其主键如果和某条逻辑删除的数据主键是相同的,那么将会操作失败,因为主键不能重复,所以需要更改逻辑删除的信息的主键值,例如在前面加个字符D,然而linq是不允许修改主键值的,为了防止并发操作引起的错误,所以要想是实
以下是我自定义的两个表实例,我目前用的是LinqToSQlite来直接实例化的 //--------------------------------------------------------------------------------------------------- // <auto-generated> // This code was generated by T4Mod
转载地址: http://www.prg-cn.com/article-4459-1.html 更新(Update) 说明:更新操作,先获取对象,进行修改操作之后,直接调 用SubmitChanges()方法即可提交。注意,这里是在同一个DataContext中,对于 不同的DataContex看下面的讲解。 1.简单形式 Customer cust = db.Customers.First(
说明:更新操作,先获取对象,进行修改操作之后,直接调用SubmitChanges()方法即可提交。注意,这里是在同一个DataContext中,对于不同的DataContex看下面的讲解。 1.简单形式 Customer cust = db.Customers.First(c => c.CustomerID == "ALFKI"); cust.ContactTitle = "Vice