Comparison of queries in SBQL and LINQ

by Kazimierz Subieta

May 2008

 

 

A comparison of LINQ queries and SBQL queries I present referring to the following XML file. We can make ODRA objects that are 1:1 compatible with the presented XML file.

 

<?xml version="1.0" encoding="UTF-8"?>

<deptemp>

 

    <Emp id="i1">

        <name>Doe</name>

        <sal>2500</sal>

        <worksIn idref="i17"></worksIn>

        <manages idref="i17"></manages>

    </Emp>

 

    <Emp id="i5">

        <name>Poe</name>

        <sal>2000</sal>

        <worksIn idref="i22"></worksIn>

    </Emp>

 

    <Emp id="i9">

        <name>Lee</name>

        <sal>900</sal>

        <address>

              <city>Rome</city>

              <street>Boogie</street>

              <house>13</house>

        </address>

        <worksIn idref="i22"></worksIn>

    </Emp>

 

    <Emp id="i55">

        <name>Bert</name>

        <sal>3000</sal>

        <address>

              <city>Paris</city>

              <street>Avenue</street>

              <house>34</house>

        </address>

        <worksIn idref="i22"></worksIn>

        <manages idref="i22"></manages>

    </Emp>

 

    <Dept id="i17">

        <dname>Trade</dname>

        <loc>Paris</loc>

        <loc>Rome</loc>

        <budget>100000</budget>

        <employs idref="i1"></employs>

        <boss idref="i1"></boss>

    </Dept>

 

    <Dept id="i22">

        <dname>Ads</dname>

        <loc>Rome</loc>

        <budget>200000</budget>

        <employs idref="i5"></employs>

        <employs idref="i9"></employs>

        <employs idref="i55"></employs>

        <boss idref="i55"></boss>

    </Dept>

 

</deptemp>

 

In SBQL queries I assume that the deptemp module is open, hence deptemp is not necessary in queries. All XML strings are wrapped into an object named _VALUE, but this is avoided in ODRA objects. Note that XML objects named worksIn, employs, manages and boss are changed during import into corresponding bidirectional pointers.  

Below I present rather typical queries. SBQL has operators such as transitive closures, fixed-point equations and operations on range numbers that are absent in SQL (and probably absent in LINQ); thus I avoid presenting such queries that are impossible to express in LINQ. SBQL is not only a query language, but a full object-oriented database programming language with all the well-known functionality (procedures, functions, classes, methods, event processing, transactions etc.) and some less-known functionality (updatable views, protocols for integrating distributed resources and others). Such functionalities are not presented in this file.

Below I present a UML-like ODRA schema that corresponds to this file.

 

 

1

Get departments together with the average salaries of their employees:

Lexical units

SBQL

Dept join avg(employs.Emp.sal)

10

LINQ

var query1 = from d in Dept

 select new

 {

   dpt = d,

   avg = (from e in d.employs

                     select e.sal).Average()

 };

30

 

LINQ forces the use of internal variables in queries (such as d and e in the above query). In SBQL, similarly to SQL, such variables can be avoided in majority of cases. Moreover, LINQ uses at least three different kinds of auxiliary naming: iteration variables (d and e), variables used in lambda notation, structure field labels (dpt and avg) and perhaps others. In SBQL there is only one semantic category of auxiliary naming that can be used in all contexts (iteration variables, variables bound by quantifiers, structure field labels, etc.). Unification of this auxiliary naming makes the language conceptually and semantically simpler, more orthogonal and better prepared for query optimization.

 

2

Get name and department name for employees earning less than 2222:

Lexical units

SBQL

(Emp where sal < 2222).(name, worksIn.Dept.dname)

17

LINQ

var query2 = from e in Emp

              where e.sal < 2222

              select new

              {

                   EmpName = e.name,

                   DeptName = e.worksIn.dname

              };

27

 

3

Get names of employees working for the department managed by Bert:

Lexical units

SBQL

(Emp where (worksIn.Dept.boss.Emp.name)= "Bert").

name

19

LINQ

var query3 = from e in Emp

            where e.worksIn.boss.name == "Bert"

            select e.name;

18

 

Note that the LINQ query avoids using Dept after worksIn  and Emp after boss, which seems to be the advantage: queries are shorter. We consider such a feature as disadvantageous for two reasons:

(1) For updating there is necessity to distinguish a reference to worksIn pointer and a reference to a Dept objects. LINQ makes such distinction impossible, thus will make difficulties with smooth extending LINQ with updating statements. Probably LINQ queries cannot be used for updating at all and cannot be passed as parameters in the call-by-reference mode.

(2) SBQL queries are more legible, because they explicitly show all the navigation path and the objects the navigation is finished. In LINQ the programmer sees only boss pointer thus must verify the schema (which could be very large) to be sure what will be the result of the navigation. SBQL convention is better for conceptual modelling.

 

 

4

Get the name of Poe's boss:

Lexical units

 

 

SBQL

(Emp where name = "Poe"). worksIn.Dept.boss.Emp.name

17

 

 

LINQ

var query4 = from e in Emp

            where e.name == "Poe"

             select e.worksIn.boss.name;

18

 

 

 

 

 

 

The same remark as above.

 

5

Names and cities of employees working in departments managed by Bert:

Lexical units

SBQL

(Dept where (boss.Emp.name) = "Bert"). employs.Emp.(name,

   if exists(address) then address.city

   else "No address")

33

LINQ

var query5 = from e in Emp

     where e.worksIn.boss.name == "Bert"

     select new

     {

        Ename = e.name,

        Ecity = (e.address == null ? "No address"

                               : e.address.city)

     };

41

 

The same remark as above. Some professionals (Ch.Date) severely criticize nulls in databases, due to the inherent inconsistency that nulls lead to. See http://www.sbql.pl/Topics/SBA%20IrregularData.html. SBQL does not introduce null values. Instead, it deals with (nested) collections having the cardinality [0..1].

 

6

Get the minimal, average and maximal number of employees in departments:

Lexical units

SBQL

(Dept.count(employs)) groupas counts.

(min(counts),avg(counts), max(counts))

27

LINQ

var counts = from d in Dept

select d.worksIn.Count();

var query6 = new

{

     Min = counts.Min(),

     Avg = counts.Average(),

     Max = counts.Max()

};

42

 

Note that LINQ subdivided a single query into two programming statements. This means shifting query processing to procedural capabilities, which is disadvantageous for conceptual modeling and for query optimizations. I am not sure what are possibilities of LINQ concerning nested queries and is it possible to develop efficient optimization methods for nested queries. SBQL nested queries are optimized by powerful methods, much more powerful than the methods recognized in SQL.

 

7

For each department get its name and the sum of salaries of employees being not bosses:

Lexical units

SBQL

Dept.(dname, sum(employs.

          (Emp where not exists(manages)).sal))

22

LINQ

var query7 = from d in Dept

     select new

     {

       DeptName = d.dname,

       StaffSalary = (from e in d.employs

           where e != d.boss

           select e.sal).Sum()

     };

38

 

8

Is it true that each department employs an employee earning the same as his/her boss?

Lexical units

SBQL

forall (Dept as d)

forsome ((d.employs.Emp minus d.boss.Emp) as e)

(e.sal = d.boss.Emp.sal)

37

LINQ

var query8 = ( from d in Dept

  select (

    from e in d.employs

    where e != d.boss && e.sal == d.boss.sal

    select e).Any()

).All(found => found);

44

 

In the above LINQ example quantifiers Any and All are non-intuitive and far from traditional mathematical and query notation. There is no explicit variables bound by quantifiers. The notation found => found is a syntactic overhead of the lambda notation that could be difficult to explain. In my opinion, lambda notation is too complicated for the average programmer. The genericity that it implies by higher-order functions in non-consumable in languages such as C# (I will present the arguments in a special report). Functional polymorphic languages such as ML, Scheme and Haskell do not enjoy commercial success, despite big effort  of academic communities. Maybe this is due to some bad marketing, maybe this is a lesson that we should  not learn again.

 

9

For each employee get the message containing his/her name and the percent of the annual budget of his/her department that is consumed by his/her monthly salary:

Lexical units

SBQL

Emp . ("Employee " + name + " consumes " +

((sal * 12 * 100)/(worksIn.Dept.budget)) +

"% of the " +

worksIn.Dept.dname + " department budget.")

37

LINQ

var query9 = from e in Emp

  select "Employee " + e.name + " consumes " +

     ((e.sal * 12 * 100) / e.worksIn.budget) +

     "% of the " + e.worksIn.dname +

     " department budget.";

39

 

10

Get cities hosting all departments:

Lexical units

SBQL

unique(deref(Dept.loc)) as deptcity

where forall Dept(deptcity in loc)

19

LINQ

var query10 = from city in Dept.

SelectMany(d => d.loc).Distinct()
where Dept.All(d => d.loc.Contains(city))
select new { City = city };

40

 

 

The use of SelectMany, Distinct, All and Contains operators looks complex and unnatural. It may require extensive training from the user.

 

 

Conclusion

 

 

Although LINQ is incomparably more famous and supported by a very rich and successful software company, I donít feel it is a better language than SBQL. You can see and compare. Currently my group is preparing a more detailed report comparing SBQL and LINQ that will concern architecture, data model, adequacy for programming very large databases, expressive power, friendly syntax, formal semantics, query optimization and other issues.