Всем привет!
Совсем недавно озадачился проблемой извлечения данных из нескольких таблиц БД, при котором обеспечивался бы 100% -й вывод колонок одной из таблиц при отсутствии записей в другой.То есть, если две таблицы связаны по ключу, и во второй таблице отсутствуют записи по этому ключу, при запросе к этим таблицам выводились колонки 1-й таблицы и вместо колонок 2-й таблицы что то вроде "NULL". Это фактически программная интерпретация LEFT JOIN в SQL.
Постараюсь показать на примере как можно реализовать LEFT JOIN в LINQ to SQL.
Cоздайте новое консольное приложение в Visual Studio. Можно использовать SharpDevelop, единственный момент - необходимо создавать проект с версией .NET Framework не меньше чем 3.5 - необходимость для использования LINQ to SQL.
В нашем примере будем использовать три таблицы данных: это авторы книг, книги и жанры. Таблица авторы будет связана с таблицей книги отношение один ко многим (для упрощения и наглядности примера случай когда у одной книги несколько авторов - не рассматривается), т.е. у одного автора может быть много написанных книг или ни одной. Таблица книги связана с таблицей авторов и с таблицей жанры отношением много к одному. Каждая написанная книга принадлежит к какому либо жанру и несколько книг могут принадлежать одному жанру.
1. В созданное консольное приложение добавляем класс и называем его "Books":
Список свойств объекта класса Books
public int Id { get; set; }
public int AuthorId { get; set; }
public int GenreId { get; set; }
public string BookName { get; set; }
Создадим метод для получения массива объектов класса Books - это будет наша имитация извлечения данных из БД:
public static Books[] GetBooks()
{
Books[] booksLst = new Books[]{
new Books{
Id = 1,
AuthorId = 1,
GenreId = 1,
BookName = "Книга №1 "},
new Books{
Id = 2,
AuthorId = 1,
GenreId = 1,
BookName = "Книга №2 "},
new Books{
Id = 3,
AuthorId = 1,
GenreId = 4,
BookName = "Книга №3 "},
new Books{
Id = 4,
AuthorId = 2,
GenreId = 1,
BookName = "Книга №4 "},
new Books{
Id = 5,
AuthorId = 2,
GenreId = 1,
BookName = "Книга №5 "},
new Books{
Id = 6,
AuthorId = 3,
GenreId = 1,
BookName = "Книга №6 "},
new Books{
Id = 7,
AuthorId = 4,
GenreId = 1,
BookName = "Книга №7 "},
new Books{
Id = 8,
AuthorId = 4,
GenreId = 1,
BookName = "Книга №8 "},
new Books{
Id = 9,
AuthorId = 5,
GenreId = 1,
BookName = "Книга №9 "},
new Books{
Id = 10,
AuthorId = 5,
GenreId = 1,
BookName = "Книга №10 "},
new Books{
Id = 11,
AuthorId = 5,
GenreId = 1,
BookName = "Книга №11"},
new Books{
Id = 12,
AuthorId = 8,
GenreId = 2,
BookName = "Книга №12"},
new Books{
Id = 13,
AuthorId = 8,
GenreId = 2,
BookName = "Книга №13"},
new Books{
Id = 14,
AuthorId = 9,
GenreId = 1,
BookName = "Книга №14"},
new Books{
Id = 15,
AuthorId = 10,
GenreId = 1,
BookName = "Книга №15"}
};
return booksLst;
}
Класс Books готов.
2. Создаем класс Authors - авторы книг:
class Authors
{
public int Id { get; set; }
public string AuthorName { get; set; }
public static Authors[] GetAuthors()
{
Authors[] authorsLst = new Authors[]
{
new Authors{
Id = 1,
AuthorName = "Автор №1"},
new Authors{
Id = 2,
AuthorName = "Автор №2"},
new Authors{
Id = 3,
AuthorName = "Автор №3"},
new Authors{
Id = 4,
AuthorName = "Автор №4"},
new Authors{
Id = 5,
AuthorName = "Автор №5"},
new Authors{
Id = 6,
AuthorName = "Автор №6"},
new Authors{
Id = 7,
AuthorName = "Автор №7"},
new Authors{
Id = 8,
AuthorName = "Автор №8"}
};
return authorsLst;
}
}
Создается по аналогии и останавливаться подобно на нем я не буду.
3. Класс Genres - жанры:
class Genres
{
public int Id { get; set; }
public string Genre { get; set; }
public static Genres[] GetGenres()
{
Genres[] genresLst = new Genres[]
{
new Genres{
Id = 1,
Genre = "Жанр №1"},
new Genres{
Id = 2,
Genre = "Жанр №2"}
};
return genresLst;
}
}
4. Теперь, когда у нас есть три источника данных для запроса LINQ to SQL, займемся этими самыми запросами:
В методе Main нашего приложения пишем:
static void Main(string[] args)
{
получаем массивы объектов книги авторы и жанры для последующих запросов:
Books[] books = Books.GetBooks();
Authors[] authors = Authors.GetAuthors();
Genres[] genres = Genres.GetGenres();
var result = from b in books
join a in authors on b.AuthorId equals a.Id into temp1
from t1 in temp1
select new
{
bookname = b.BookName,
authorName = t1.AuthorName
};
foreach (var item in result)
{
Console.WriteLine("{0} {1}", item.authorName, item.bookname);
}
Console.ReadKey();
}
В первом запросе используем только массивы books и authors. Необходимо найти все книги и авторов, которые их написали.
Результат выполнения:
static void Main(string[] args)
{
получаем массивы объектов книги авторы и жанры для последующих запросов:
Books[] books = Books.GetBooks();
Authors[] authors = Authors.GetAuthors();
Genres[] genres = Genres.GetGenres();
var result = from b in books
join a in authors on b.AuthorId equals a.Id into temp1
from t1 in temp1.DefaultIfEmpty()
select new
{
bookname = b.BookName,
authorName = t1 == null ? "null" : t1.AuthorName
};
foreach (var item in result)
{
Console.WriteLine("{0} {1}", item.authorName, item.bookname);
}
Console.ReadKey();
}
Результат выполнения:
Для того, чтобы отображались все столбцы из массива author, необходимо использовать функцию DefaultIfEmpty(), которая есть в LINQ TO SQL.
Конструкция authorName = t1 == null ? "null" : t1.AuthorName проверяет на NULL объект t1 и подставляет "null" типа string в результирующий набор. "null" я выбрал для наглядности, можно подставлять empty, но проверку делать необходимо т.к. иначе компилятор ругается на отсутствие объекта по ссылки при выполнении данного кода.
В следующем запросе добавим дополнительное условие:
static void Main(string[] args)
{
получаем массивы объектов книги авторы и жанры для последующих запросов:
Books[] books = Books.GetBooks();
Authors[] authors = Authors.GetAuthors();
Genres[] genres = Genres.GetGenres();
var result = from b in books
join a in authors on b.AuthorId equals a.Id into temp1
from t1 in temp1.DefaultIfEmpty()
where b.BookName == "Книга №15"
select new
{
bookname = b.BookName,
authorName = t1 == null ? "null" : t1.AuthorName
};
foreach (var item in result)
{
Console.WriteLine("{0} {1}", item.authorName, item.bookname);
}
Console.ReadKey();
}
Результат выполнения:
static void Main(string[] args)
{
получаем массивы объектов книги авторы и жанры для последующих запросов:
Books[] books = Books.GetBooks();
Authors[] authors = Authors.GetAuthors();
Genres[] genres = Genres.GetGenres();
var result = from b in books
join a in authors on b.AuthorId equals a.Id into temp1
from t1 in temp1.DefaultIfEmpty()
join g in genres on b.GenreId equals g.Id into temp2
from t2 in temp2.DefaultIfEmpty()
select new
{
bookname = b.BookName,
genre = t2 == null ? "null" : t2.Genre,
authorName = t1 == null ? "null" : t1.AuthorName
};
foreach (var item in result)
{
Console.WriteLine("{0} {1} {2}",item.authorName,item.bookname,item.genre);
}
Console.ReadKey();
}
Всем спасибо за внимание и успехов!
Совсем недавно озадачился проблемой извлечения данных из нескольких таблиц БД, при котором обеспечивался бы 100% -й вывод колонок одной из таблиц при отсутствии записей в другой.То есть, если две таблицы связаны по ключу, и во второй таблице отсутствуют записи по этому ключу, при запросе к этим таблицам выводились колонки 1-й таблицы и вместо колонок 2-й таблицы что то вроде "NULL". Это фактически программная интерпретация LEFT JOIN в SQL.
Постараюсь показать на примере как можно реализовать LEFT JOIN в LINQ to SQL.
Cоздайте новое консольное приложение в Visual Studio. Можно использовать SharpDevelop, единственный момент - необходимо создавать проект с версией .NET Framework не меньше чем 3.5 - необходимость для использования LINQ to SQL.
В нашем примере будем использовать три таблицы данных: это авторы книг, книги и жанры. Таблица авторы будет связана с таблицей книги отношение один ко многим (для упрощения и наглядности примера случай когда у одной книги несколько авторов - не рассматривается), т.е. у одного автора может быть много написанных книг или ни одной. Таблица книги связана с таблицей авторов и с таблицей жанры отношением много к одному. Каждая написанная книга принадлежит к какому либо жанру и несколько книг могут принадлежать одному жанру.
1. В созданное консольное приложение добавляем класс и называем его "Books":
Список свойств объекта класса Books
public int Id { get; set; }
public int AuthorId { get; set; }
public int GenreId { get; set; }
public string BookName { get; set; }
Создадим метод для получения массива объектов класса Books - это будет наша имитация извлечения данных из БД:
public static Books[] GetBooks()
{
Books[] booksLst = new Books[]{
new Books{
Id = 1,
AuthorId = 1,
GenreId = 1,
BookName = "Книга №1 "},
new Books{
Id = 2,
AuthorId = 1,
GenreId = 1,
BookName = "Книга №2 "},
new Books{
Id = 3,
AuthorId = 1,
GenreId = 4,
BookName = "Книга №3 "},
new Books{
Id = 4,
AuthorId = 2,
GenreId = 1,
BookName = "Книга №4 "},
new Books{
Id = 5,
AuthorId = 2,
GenreId = 1,
BookName = "Книга №5 "},
new Books{
Id = 6,
AuthorId = 3,
GenreId = 1,
BookName = "Книга №6 "},
new Books{
Id = 7,
AuthorId = 4,
GenreId = 1,
BookName = "Книга №7 "},
new Books{
Id = 8,
AuthorId = 4,
GenreId = 1,
BookName = "Книга №8 "},
new Books{
Id = 9,
AuthorId = 5,
GenreId = 1,
BookName = "Книга №9 "},
new Books{
Id = 10,
AuthorId = 5,
GenreId = 1,
BookName = "Книга №10 "},
new Books{
Id = 11,
AuthorId = 5,
GenreId = 1,
BookName = "Книга №11"},
new Books{
Id = 12,
AuthorId = 8,
GenreId = 2,
BookName = "Книга №12"},
new Books{
Id = 13,
AuthorId = 8,
GenreId = 2,
BookName = "Книга №13"},
new Books{
Id = 14,
AuthorId = 9,
GenreId = 1,
BookName = "Книга №14"},
new Books{
Id = 15,
AuthorId = 10,
GenreId = 1,
BookName = "Книга №15"}
};
return booksLst;
}
Класс Books готов.
2. Создаем класс Authors - авторы книг:
class Authors
{
public int Id { get; set; }
public string AuthorName { get; set; }
public static Authors[] GetAuthors()
{
Authors[] authorsLst = new Authors[]
{
new Authors{
Id = 1,
AuthorName = "Автор №1"},
new Authors{
Id = 2,
AuthorName = "Автор №2"},
new Authors{
Id = 3,
AuthorName = "Автор №3"},
new Authors{
Id = 4,
AuthorName = "Автор №4"},
new Authors{
Id = 5,
AuthorName = "Автор №5"},
new Authors{
Id = 6,
AuthorName = "Автор №6"},
new Authors{
Id = 7,
AuthorName = "Автор №7"},
new Authors{
Id = 8,
AuthorName = "Автор №8"}
};
return authorsLst;
}
}
Создается по аналогии и останавливаться подобно на нем я не буду.
3. Класс Genres - жанры:
class Genres
{
public int Id { get; set; }
public string Genre { get; set; }
public static Genres[] GetGenres()
{
Genres[] genresLst = new Genres[]
{
new Genres{
Id = 1,
Genre = "Жанр №1"},
new Genres{
Id = 2,
Genre = "Жанр №2"}
};
return genresLst;
}
}
4. Теперь, когда у нас есть три источника данных для запроса LINQ to SQL, займемся этими самыми запросами:
В методе Main нашего приложения пишем:
static void Main(string[] args)
{
получаем массивы объектов книги авторы и жанры для последующих запросов:
Books[] books = Books.GetBooks();
Authors[] authors = Authors.GetAuthors();
Genres[] genres = Genres.GetGenres();
var result = from b in books
join a in authors on b.AuthorId equals a.Id into temp1
from t1 in temp1
select new
{
bookname = b.BookName,
authorName = t1.AuthorName
};
foreach (var item in result)
{
Console.WriteLine("{0} {1}", item.authorName, item.bookname);
}
Console.ReadKey();
}
В первом запросе используем только массивы books и authors. Необходимо найти все книги и авторов, которые их написали.
Результат выполнения:
Видно, что у нас есть книги №№ 14,15. Но они не попали в результат, т. к. для них не оказалось соответствующего автора. А что делать, если их все равно необходимо вывести в результат запроса? Переделаем запрос:
static void Main(string[] args)
{
получаем массивы объектов книги авторы и жанры для последующих запросов:
Books[] books = Books.GetBooks();
Authors[] authors = Authors.GetAuthors();
Genres[] genres = Genres.GetGenres();
var result = from b in books
join a in authors on b.AuthorId equals a.Id into temp1
from t1 in temp1.DefaultIfEmpty()
select new
{
bookname = b.BookName,
authorName = t1 == null ? "null" : t1.AuthorName
};
foreach (var item in result)
{
Console.WriteLine("{0} {1}", item.authorName, item.bookname);
}
Console.ReadKey();
}
Результат выполнения:
Для того, чтобы отображались все столбцы из массива author, необходимо использовать функцию DefaultIfEmpty(), которая есть в LINQ TO SQL.
Конструкция authorName = t1 == null ? "null" : t1.AuthorName проверяет на NULL объект t1 и подставляет "null" типа string в результирующий набор. "null" я выбрал для наглядности, можно подставлять empty, но проверку делать необходимо т.к. иначе компилятор ругается на отсутствие объекта по ссылки при выполнении данного кода.
В следующем запросе добавим дополнительное условие:
static void Main(string[] args)
{
получаем массивы объектов книги авторы и жанры для последующих запросов:
Books[] books = Books.GetBooks();
Authors[] authors = Authors.GetAuthors();
Genres[] genres = Genres.GetGenres();
var result = from b in books
join a in authors on b.AuthorId equals a.Id into temp1
from t1 in temp1.DefaultIfEmpty()
where b.BookName == "Книга №15"
select new
{
bookname = b.BookName,
authorName = t1 == null ? "null" : t1.AuthorName
};
foreach (var item in result)
{
Console.WriteLine("{0} {1}", item.authorName, item.bookname);
}
Console.ReadKey();
}
Результат выполнения:
Выбрали авторов и книгу, которая называется "Книга №15". Автора у нее пока в массиве нету.
Ну и напоследок запрос к трем массивам: авторы, книги и жанры.
{
получаем массивы объектов книги авторы и жанры для последующих запросов:
Books[] books = Books.GetBooks();
Authors[] authors = Authors.GetAuthors();
Genres[] genres = Genres.GetGenres();
var result = from b in books
join a in authors on b.AuthorId equals a.Id into temp1
from t1 in temp1.DefaultIfEmpty()
join g in genres on b.GenreId equals g.Id into temp2
from t2 in temp2.DefaultIfEmpty()
select new
{
bookname = b.BookName,
genre = t2 == null ? "null" : t2.Genre,
authorName = t1 == null ? "null" : t1.AuthorName
};
foreach (var item in result)
{
Console.WriteLine("{0} {1} {2}",item.authorName,item.bookname,item.genre);
}
Console.ReadKey();
}
Результат выполнения:
По результату видно что не у всех книг есть автор и жанр. Прошу заметить, что тут реализован именно LEFT JOIN а не FULL JOIN. Т.е. если будет какой то автор у которого нет книг, (в колонке авторы будет информация а в колонке книги null) то такая запись просто не выведется в результат. Чтобы было понятнее я переставил местами колонки книга и автор:
