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)
);
  1. 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 value
In 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-query

4. Select partial fields from a table
Just 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 tables
Linq 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.
  • inner join
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;
  • left outer join
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;
  • right outer join
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;
  • full outer join
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
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 rows
In 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

Last edited Jun 19, 2012 at 2:37 AM by robinz1001, version 13

Comments

No comments yet.