RayBullet data access library supports almost all Linq syntax.
To explain the query features the library provides, we use three tables for example.
CREATE TABLE Carrier (
Id int IDENTITY NOT NULL,
Code varchar(20) NULL,
Name1 nvarchar(50) NULL,
IsDisabled bit NULL,
Sequence smallint NULL,
RemoteCharge float(53) NULL,
FuelSurcharge float(53) NULL,
VolumeDivisor int NULL,
Comment nvarchar(max) NULL,
CONSTRAINT PK_Carrier_Tbl PRIMARY KEY (Id)
);
CREATE TABLE CarrierZone (
Id int IDENTITY NOT NULL,
Zone int NULL, CountryCode char(2) NULL,
CarrierId int NOT NULL,
Direction int NULL,
CONSTRAINT PK_CarrierZone_Tbl PRIMARY KEY (Id)
);
CREATE TABLE Country (
Code char(2) NOT NULL,
Name1 nvarchar(100) NULL,
RegionCode char(3) NOT NULL,
CONSTRAINT PK__S2_Country PRIMARY KEY (Code)
);
- Simple condition and ordering
"Where" and "Order by" are the most usual things we do with SQL. Here is a simple example. But when you write it, you must know you are writing .Net code instead of SQL statement, so forget SQL syntax at this moment.
var q = from p in DataContext.CreateExecutor().Query<Carrier>()
where !p.IsDisabled && p.RemoteCharge>0
orderby p.Sequence
select p;
1. Search by a string"StartsWith", "EndsWith" and "Contains" are supported by the library.
var q = from p in DataContext.CreateExecutor().Query<Carrier>()
where p.Name1.StartsWith("U")
orderby p.Sequence
select p;
var q1 = from p in DataContext.CreateExecutor().Query<Carrier>()
where p.Name1.EndsWith("S")
orderby p.Sequence
select p;
var q2 = from p in DataContext.CreateExecutor().Query<Carrier>()
where p.Name1.Contains("P")
orderby p.Sequence
select p;
Of course, you can also do in opposite way.
string names = "UPS,DHL";
var q = from p in DataContext.CreateExecutor().Query<Carrier>()
where names.Contains(p.Name1)
orderby p.Sequence
select p;
2. Search by a list of valueIn SQL statement, we can use "IN" keyword to get records in which value of a field is in a list of value. We also support this, but the values must be stored in an array or a object support IEnumerable interface.
string[] names = new string[] {"UPS","DHL","TNT" };
var q = from p in DataContext.CreateExecutor().Query<Carrier>()
where names.Contains(p.Code)
orderby p.Sequence
select p;
3. Search in a sub-query4. Select partial fields from a tableJust like Linq to SQL, we can select only a few fields of a table.
If you are going to get only one column, you can just select a property like this,
var q = from p in DataContext.CreateExecutor().Query<Carrier>()
orderby p.Sequence
select p.Name1;
To get more than one column, the syntax will be a little confused. Usually, we use anonymous class.
var q = from p in DataContext.CreateExecutor().Query<Carrier>()
orderby p.Sequence
select new { p.Id, p.Name1 };
And we can also new a class with the same type being queried.
var q = from p in DataContext.CreateExecutor().Query<Carrier>()
orderby p.Sequence
select new Carrier() { Id = p.Id, Name1 = p.Name1 };
But in this case, don't forget only the properties been initialized have valid values.
Actually, the class type we new can be other type not only the type we query. And of course, we can use expression in the initializer.
class CarrierPartial {
public int Id { get; set; }
public string CodeAndName { get; set; }
public double FuelChargePercent { get; set; }
};
var q = from p in DataContext.CreateExecutor().Query<Carrier>()
orderby p.Sequence
select new CarrierPartial ()
{
Id = p.Id,
CodeAndName = p.Code+p.Name1,
FuelChargePercent = p.FuelSurcharge * 100
};
5. Join tablesLinq does provide a syntax to support "INNER JOIN", but it is a little difficult to use and understand how to do "OUTER JOIN". In our library, we don't use the syntax used in Linq to SQL when we make an "OUTER JOIN". Actually, we use a more simple way and we support "LEFT", "RIGHT", and "FULL" "OUTER JOIN" in the similar syntax. We use "DefaultIfEmpty" method to specify if a table being joined can be no match records.
var q = from p in DataContext.CreateExecutor().Query<Country>()
join p1 in DataContext.CreateExecutor().Query<CarrierZone>() on p.Code equals p1.CountryCode
join p2 in DataContext.CreateExecutor().Query<Carrier>() on p1.CarrierId equals p2.Id
where p2.Code == "UPS" and p1.Zone == 1
select p;
var q = join p1 in DataContext.CreateExecutor().Query<Carrier>()
join p2 in DataContext.CreateExecutor().Query<CarrierZone>().DefaultIfEmpty() on p1.CarrierId equals p2.Id
where p2.CountryCode == ''GB"
select p1;
var q = join p1 in DataContext.CreateExecutor().Query<CarrierZone>().DefaultIfEmpty()
join p2 in DataContext.CreateExecutor().Query<Carrier>() on p1.CarrierId equals p2.Id
where p1.CountryCode == ''GB"
select p2;
var q = from p in DataContext.CreateExecutor().Query<Country>().DefaultIfEmpty()
join p1 in DataContext.CreateExecutor().Query<CarrierZone>().DefaultIfEmpty() on p.Code equals p1.CountryCode
select new { p.Code, p.Name1, p1.CarrierId, p1.Zone };
Cross join does not use "join" keyword
var q = from p in DataContext.CreateExecutor().Query<Country>()
from p1 in DataContext.CreateExecutor().Query<CarrierZone>()
where p.Code == p1.CountryCode
select new { p.Code, p.Name1, p1.CarrierId, p1.Zone };
6. Paging data rowsIn most of cases, we get all data rows from a table even if we use a paging data grid. Because data grid has paging supports usually. However, it will not be smart to get all data rows from a table with more than hundred thousands of records. Linq syntax has no a method which can get a segment of records, but we can use "Skip" and "Take" methods to do the same thing.
int page = 5;
int pageSize = 10;
var q = from p in DataContext.CreateExecutor().Query<Country>()
orderby p.Code
select p;
q = q.Skip((page - 1) * pageSize).Take(pageSize);
7. Aggregate functions