Manipulate data with Telerik Open Access

Content:
1. CRUD operations
   - Read
   - Create
   - Update
   - Delete
   - Concurency handling during save
2. Helpful features
   - Automatic join with WHERE
   - Navigation properties
3. Thinks to be aware of
   - Joins with data
   - Where clause location matters
   - Unexpected order by in SQL
   - Join tables in model

---

Understanding of LINQ basics is recommended before learning Telerik Open Access.

How to start using Telerik Data Access on Telerik website
CRUD operation on Telerik website

C#
using (EntitiesModel dbContext = new EntitiesModel())
{
    foreach (var person in dbContext.Persons)
    {
        string firstName = person.FirstName;
    }
}

C#
using (EntitiesModel dbContext = new EntitiesModel())
{
    Persons newPerson = new Persons();
    newPerson.FirstName = "New";
    dbContext.Add(newPerson);
    dbContext.SaveChanges();
}

C#
using (EntitiesModel dbContext = new EntitiesModel())
{
    Persons person = dbContext.Persons.Where(p => p.Id==1).FirstOrDefault();
    person.LastName += "++";
    dbContext.SaveChanges();
}

C#
using (EntitiesModel dbContext = new EntitiesModel())
{
    IQueryable<Persons> personsToDelete = dbContext.Persons.Where(p => p.Id > 1);
    dbContext.Delete(personsToDelete);
    // dbContext.Delete(personsToDelete.FirstOrDefault()); One record can be deleted as well
    dbContext.SaveChanges(Telerik.OpenAccess.ConcurrencyConflictsProcessingMode.AggregateAll);
}

C#
// executes all SQL statements regardless of error 
dbContext.SaveChanges(Telerik.OpenAccess.ConcurrencyConflictsProcessingMode.AggregateAll);

// stops the processing after the first error occurs 
dbContext.SaveChanges(Telerik.OpenAccess.ConcurrencyConflictsProcessingMode.StopOnFirst);

Normally it possibl eto write join between 2 tables like below

C#
var people2 = dbContext.Persons
    .Join(
    dbContext.Departments,
    p => p.Department_id,
    d => d.Id,
    (p, d) => new { name = p.FirstName, name1 = p.LastName,  d.Name }
    );

Telerik offers another approach without JOIN clause

SQL
var people3 = from p in dbContext.Persons
              from d in dbContext.Departments
              where p.Department_id == d.Id  // works like JOIN
              select new { p.FirstName, p.LastName, d.Name };

The syntax above will generate a SQL Statement with CROSS JOIN (which with WHERE clause will behave like INNER JOIN)

SQL
SELECT a.[firstName] AS COL1, a.[lastName] AS COL2, b.[name] AS COL3 
FROM [MY_SCHEMA].[persons] a 
CROSS JOIN [MY_SCHEMA].[departments] AS b 
WHERE a.[department_id] = b.[id] 

It is possible to use Navigation to access data in related tables

C#
foreach (Persons person in dbContext.Persons)
{
    string departmenName = person.Departments.Name;
}

It is necessary to be aware:
1. If DepartmentId is null then person.Departments.Name will throw an exception
2. Telerik will generate one SQL statement to retrieve Person data and one SQL statement for every unique department ID.
If more people have the same Department_ID then Telerik will generate only one statement for the ID as an argument for a stored procedure.

SQL
SELECT a.[id] AS COL1, a.[birthday] AS COL2, a.[department_id] AS COL3, a.[department_id] AS COL4, a.[firstName] AS COL5, a.[height] AS COL6, a.[lastName] AS COL7 
FROM [MY_SCHEMA].[persons] a 
ORDER BY COL1 

declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@p0 int',N'SELECT [head] AS COL1, [name] AS COL2  FROM [MY_SCHEMA].[departments] WHERE [id] = @p0',@p0=1
select @p1

declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@p0 int',N'SELECT [head] AS COL1, [name] AS COL2  FROM [MY_SCHEMA].[departments] WHERE [id] = @p0',@p0=2
select @p1

Telerik enables writing queries that join data in memeory with data in SQL database

C#
var persons5 = (from p in dbContext.Persons
                where p.FirstName == ("John")
                join d in deptIds on p.Department_id equals d
                select p)
                .ToList();

As you can see join happens in memory. Generated SQL statement:

SQL
SELECT a.[id] AS COL1, a.[birthday] AS COL2, a.[department_id] AS COL3, a.[department_id] AS COL4, a.[firstName] AS COL5, a.[height] AS COL6, a.[lastName] AS COL7 
FROM [MY_SCHEMA].[persons] a 
WHERE a.[firstName] = 'John' 
ORDER BY COL1 

The example above shows that WHERE clause was present. It is the case only when WHERE clause was present before before JOIN.

C#
List<int> deptIds = new List<int> { 1, 2 }; // user selects ids in UI

var persons6 = (from p in dbContext.Persons
                join d in deptIds on p.Department_id equals d
                where p.FirstName == ("John")
                select p)
                .ToList();

When WHERE is after JOIN, it will be generated in SQL statement, which will cause performance problems with large data.

SQL
SELECT a.[id] AS COL1, a.[birthday] AS COL2, a.[department_id] AS COL3, a.[department_id] AS COL4, a.[firstName] AS COL5, a.[height] AS COL6, a.[lastName] AS COL7 
FROM [MY_SCHEMA].[persons] a 
ORDER BY COL1 

When you write simple queury to read data from a table, Telerik adds ORDER BY clause.

C#
var persons12 = (from p in dbContext.Persons
                 select p
                ).ToList();

ORDER BY clause could cause perfromance problems with large data when for example data is queried from a View and telerik picks a column that does not have an index.

SQL
SELECT a.[id] AS COL1, a.[birthday] AS COL2, a.[department_id] AS COL3, a.[department_id] AS COL4, a.[firstName] AS COL5, a.[height] AS COL6, a.[lastName] AS COL7 
FROM [MY_SCHEMA].[persons] a 
ORDER BY COL1 

If instead of full table specific column is specified

C#
var persons13 = (from p in dbContext.Persons
                 select new { p.LastName }
                ).ToList();

then ORDER BY clause disaperars.

SQL
SELECT a.[lastName] AS COL1                  
FROM [MY_SCHEMA].[persons] a

If multiple columns are selected, ORDER BY is not present either.

C#
var persons14 = (from p in dbContext.Persons
                 select new { p.FirstName, p.LastName }
                ).ToList();

SQL
SELECT a.[firstName] AS COL1, a.[lastName] AS COL2 
FROM [MY_SCHEMA].[persons] a

If ORDER BY clause is added then Telerik will enearte SQL respecting that clause and still adding autodetected column.

C#
var persons15 = (from p in dbContext.Persons
                 orderby p.LastName
                 select p
                ).ToList();

SQL
SELECT a.[id] AS COL1, a.[birthday] AS COL2, a.[department_id] AS COL3, a.[department_id] AS COL4, a.[firstName] AS COL5, a.[height] AS COL6, a.[lastName] AS COL7 
FROM [MY_SCHEMA].[persons] a 
ORDER BY COL7, COL1 

If you have two tables with n to n relationship like

SQL
CREATE TABLE [MY_SCHEMA].[persons](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[firstName] [varchar](20) NULL,
	[lastName] [varchar](20) NOT NULL,
)  

CREATE TABLE [MY_SCHEMA].[roles](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,  -- e.g. Manager, Engineer, Accountant
	[Description] [nvarchar](max) NULL,
)

and so called joined table that defines the relationship

SQL
CREATE TABLE [MY_SCHEMA].[persons_roles](
	[Person_ID] [int] NOT NULL,
	[Role_ID] [int] NOT NULL
)

then Telerik designer will NOT generate Entity for the helper table persons_roles. Instead Telerik will generate navigation property Roles in Person(s) class

As result of that behavior, you need to write data that would normally required writing a JOIN, you need to take a different approach. For example, if you want to read all employees who belong to manager role you need to write query like this

C#
var personsInRole = dbContext.Persons
   .Where(p => p.Roles.Where(role => role.Name == "Manager").Count() > 0 );

If you need the helper table to be presented in data model, then if you add a dummy column to the table then Telerik will generate Entity for the table. It of course mean, that query join queries will not work anymore, because the naviagtion property disappears. You need to write standard joins.

Word from Telerik