DapperCoreLinqToQuery 0.0.4
See the version list below for details.
dotnet add package DapperCoreLinqToQuery --version 0.0.4
NuGet\Install-Package DapperCoreLinqToQuery -Version 0.0.4
<PackageReference Include="DapperCoreLinqToQuery" Version="0.0.4" />
paket add DapperCoreLinqToQuery --version 0.0.4
#r "nuget: DapperCoreLinqToQuery, 0.0.4"
// Install DapperCoreLinqToQuery as a Cake Addin
#addin nuget:?package=DapperCoreLinqToQuery&version=0.0.4
// Install DapperCoreLinqToQuery as a Cake Tool
#tool nuget:?package=DapperCoreLinqToQuery&version=0.0.4
Linq To SQL Queries
The aim of the project is to create SQL queries via LINQ. Specifically for Dapper lovers.
Prerequisite
using System.ComponentModel.DataAnnotaitions;
using System.Data.SqlConnection;
using Dapper; //(required when you need to fetch results)
Installation
Just clone / download / fork and start using it in your project.
Usage
Entities/Models <br/> Supplier.cs
[Table("Supplier")]
public class Supplier
{
[Identity(AutoGenerated = true)]
[Column("Id")]
public int Id { get; set; }
[Required]
public string CompanyName { get; set; }
public string ContactName { get; set; }
public string ContactTitle { get; set; }
public string City { get; set; }
public string Country { get; set; }
public string Phone { get; set; }
public string Fax { get; set; }
[Ignore]
public IList<Product> Products { get; set; }
[Ignore]
public OrderItem Order { get; set; }
}
Product.cs
public class Product
{
[Identity(AutoGenerated = true)]
public int Id { get; set; }
public string ProductName { get; set; }
[ForeignKey("SupplierId")]
public int SupplierId { get; set; }
public string UnitPrice { get; set; }
public string Package { get; set; }
public string IsDiscontinued { get; set; }
public Supplier SupplierInfo { get; set; }
}
OrderItem.cs
[Table("OrderItem")]
public class OrderItem
{
public int Id { get; set; }
public int OrderId { get; set; }
public int ProductId { get; set; }
public float UnitPrice { get; set; }
public int Quantity { get; set; }
}
Create Connection to database
var conn = new SqlConnection("Server=[YourServer];Database=[YouDatabase];Trusted_Connection=True;");
Simple Select
//To Generate Query for SQL Server 2012 syntax
var query = conn.Select<Supplier>()
.Columns(c => c.All())
.ToSqlServer2012Query();
//To Generate Query for MySQL
var query = conn.Select<Supplier>()
.Columns(c => c.All())
.ToMySqlQuery();
//You can also fetch the result
var suppliers = conn.Select<Supplier>()
.Columns(c => c.All())
.ToResult();
Select with Where
var suppliersInStockholm = conn.Select<Supplier>()
.Columns(c => c.All())
.Where(x=>x.Column(c=>c.City).Contains("Stockholm"))
.ToResult();
Select with Join
//Fetch all products for all suppliers
var productsBySupplier = conn.Select<Product>()
.Columns(c => c.All())
.Join(j => j.Column<Supplier>(c => c.Id, JoinTypes.InnerJoin).With(s => s.SupplierId))
.ToResult();
var productOfSuppliersInStockholm = conn.Select<Product>()
.Columns(c => c.All())
.Join(j => j.Column<Supplier>(c => c.Id, JoinTypes.InnerJoin).With(s => s.SupplierId))
.Where(x => x.Column<Supplier>(c => c.City).Contains("Stockholm"))
.ToResult();
Select with Aggregated function
var suppliersInStockholm = conn.Select<Supplier>()
.Columns(c => c.Count(c=>c.Id).All())
.Where(x=>x.Column(c=>c.City).Contains("Stockholm"))
.ToResult();
There are more options with complex and easy joins Please use and find it out
To insert data
conn.Insert().Entity(new Supplier
{
Id=1,
CompanyName = "Supplier Company",
City = "Stockholm"
}).Execute(IsolationLevel.ReadCommitted);
Update Query
conn.Update<Supplier>()
.Columns(c => c
.Set(x => x.CompanyName, "A new Company again")
)
.Join(j=>j
.Column<Product>(p=>p.SupplierId,JoinTypes.InnerJoin).With(c=>c.Id)
)
.Where(w => w
.Column<Product>(x => x.ProductName)
.EqualsTo("Test")
)
.Execute(IsolationLevel.ReadCommitted);
To delete data
conn.Delete<Supplier>()
.Where(c => c.Column(x => x.CompanyName).EqualsTo("Some Company"))
.ExecuteSqlServerQuery(IsolationLevel.ReadCommitted);
Contributing
- Fork it!
- Create your feature branch:
git checkout -b my-new-feature
- Commit your changes:
git commit -am 'Add some feature'
- Push to the branch:
git push origin my-new-feature
- Submit a pull request 😄
Credits
Riyasat Ali - https://www.linkedin.com/in/riyasat-ali/
Limitations
This project doesn't cover more complex scenarios but still you can use it for many simple to mid level complex queries. If you have more ideas please feel free to fork and please update in this repository. Thanks
License
Copyright 2017 Riyasat Ali and contributors
Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.
Conditions
Add Credits to me in your project
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 was computed. net5.0-windows was computed. net6.0 was computed. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. |
.NET Core | netcoreapp3.0 was computed. netcoreapp3.1 was computed. |
.NET Standard | netstandard2.1 is compatible. |
MonoAndroid | monoandroid was computed. |
MonoMac | monomac was computed. |
MonoTouch | monotouch was computed. |
Tizen | tizen60 was computed. |
Xamarin.iOS | xamarinios was computed. |
Xamarin.Mac | xamarinmac was computed. |
Xamarin.TVOS | xamarintvos was computed. |
Xamarin.WatchOS | xamarinwatchos was computed. |
-
.NETStandard 2.1
- Dapper (>= 2.0.90)
- Microsoft.Data.SqlClient (>= 3.0.0)
- System.ComponentModel.Annotations (>= 4.4.0)
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.