Рецепты на языке C#

Практикум по составлению запросов на языках SQL и LINQ ↩︎

Описание

Практикум состоит из двух этапов. На первом этапе нужно правильно составить SQL-запросы по заданиям на сайте. На втором этапе следует организовать источники данных, реализующие интерфейс IEnumerable или IQueryable, и составить запросы к этим источникам на языке LINQ.

Формализованная версия данного практикума может выглядеть следующим образом:
Выбрать на сайте любые n заданий k-го уровня сложности (на рис. 1 в скобках после номера задания указан уровень сложности) и составить:

SQL-запросы,
LINQ-запросы в текучем синтаксисе или в синтаксисе выражений запросов.

Учитывая, что k меняется от 1 до 4, то всего может быть 4 пакета заданий с различными n.

Рис. 1. Выпадающий список для выбора задания

Пример

В качестве примера рассмотрим решения задачи №57 второго уровня сложности, взятого из сайта.

Краткая информация о базе данных “Корабли”

Рассматривается БД кораблей, участвовавших во второй мировой войне. Имеются следующие отношения:
Classes (class, type, country, numGuns, bore, displacement)
Ships (name, class, launched)
Battles (name, date)
Outcomes (ship, battle, result)
Корабли в «классах» построены по одному и тому же проекту, и классу присваивается либо имя первого корабля, построенного по данному проекту, либо названию класса дается имя проекта, которое не совпадает ни с одним из кораблей в БД. Корабль, давший название классу, называется головным. Отношение Classes содержит имя класса, тип (bb для боевого (линейного) корабля или bc для боевого крейсера), страну, в которой построен корабль, число главных орудий, калибр орудий (диаметр ствола орудия в дюймах) и водоизмещение ( вес в тоннах). В отношении Ships записаны название корабля, имя его класса и год спуска на воду. В отношение Battles включены название и дата битвы, в которой участвовали корабли, а в отношении Outcomes – результат участия данного корабля в битве (потоплен-sunk, поврежден - damaged или невредим - OK).

Замечания:
1) В отношение Outcomes могут входить корабли, отсутствующие в отношении Ships.
2) Потопленный корабль в последующих битвах участия не принимает.

Рис. 2. Схема базы данных

Задание

Для классов, имеющих потери в виде потопленных кораблей и не менее 3 кораблей в базе данных, вывести имя класса и число потопленных кораблей.

Решение на языке SQL

SELECT class, COUNT(ship) count_sunked
FROM (SELECT name, class FROM ships
      UNION
      SELECT ship, ship FROM outcomes) t
LEFT JOIN outcomes ON name = ship AND result = 'sunk'
GROUP BY class
HAVING COUNT(ship) > 0 AND COUNT(*) > 2

Решение на языке LINQ

var Union = Ships.Select(_ => new { Ship = _.Name, Class = _.Class }).
Union(Outcomes.Select(_ => new { Ship = _.Ship, Class = _.Ship.ToString() }));

var LeftJoin = from u in Union
   join o in Outcomes.Where(_ => _.Result == "sunk") on u.Ship equals o.Ship into gj
   from subpet in gj.DefaultIfEmpty()
   select new { Class = u.Class, Ship = subpet.Ship ?? String.Empty, Result = subpet.Result ?? String.Empty };
 
var ResultGroupBy = from lj in LeftJoin
   group lj by lj.Class into g
   let CountSunked = g.Where(_ => _.Ship != String.Empty).Count()
   where CountSunked > 0 && g.Count() > 2
   select new
  {
     Class = g.Key,
     CountSunked,
  };

LINQ можно использовать для левого внешнего соединения, вызвав метод DefaultIfEmpty на основании результатов группового соединения (см. https://docs.microsoft.com/ru-ru/dotnet/csharp/linq/perform-left-outer-joins).

Проверка результатов

Чтобы убедиться, что код на LINQ составлен правильно, можно воспользоваться приложением LINQPad и проделать следующие шаги:

1) Сгенерировать базу данных, воспользовавшись скриптом из сайта (cм. рис. 4-6).

Для выполнения данного шага требуется предварительно установить СУБД Microsoft SQL Server. Так как установка полноценного сервера является затратной процедурой, поэтому можно ограничиться компонентом SQL Server Express LocalDB, инсталляцию которого несложно провести из Visual Studio (см. рис. 3).

Рис. 3. SQL Server Express LocalDB