Left join with grouping via linq2db
I have the following tables
class Directory
{
public long Id { get; set;}
public string Path { get; set;}
public IEnumerable<File> Files { get; set;}
}
class File
{
public long Id { get; set;}
public long DirectoryId { get; set; }
public string Name { get; set; }
public Directory Directory { get; set; }
}
How can I get directories grouped by id with corresponding files using left join and linq2db in one request?
I think it should be something like this
var query = from d in db.Direcories
join f in db.Files on d.Id equals f.DirectoryId into items
from i in items.DefaultIfEmpty()
group i by new { d } into g
select new { Directory = g.Key, Files = ????? };
var result = query.ToList();
but i dont know how to get files from group
source to share
First of all, I highly recommend creating data model classes using T4 templates, so take a look at this project: linq2db / t4models.I also recommend watching the video here: https://github.com/linq2db/linq2db/wiki .
After my data model is generated with T4, it looks something like this:
public partial class TestDb : LinqToDB.Data.DataConnection
{
public ITable<Directory> Directories { get { return this.GetTable<Directory>(); } }
public ITable<File> Files { get { return this.GetTable<File>(); } }
}
[Table(Schema="dbo", Name="Directory")]
public partial class Directory
{
[PrimaryKey, Identity] public int ID { get; set; } // int
[Column, NotNull ] public string Path { get; set; } // varchar(max)
#region Associations
/// <summary>
/// FK_File_Directory_BackReference
/// </summary>
[Association(ThisKey="ID", OtherKey="DirectoryID", CanBeNull=true, IsBackReference=true)]
public List<File> Files { get; set; }
#endregion
}
[Table(Schema="dbo", Name="File")]
public partial class File
{
[PrimaryKey, Identity] public int ID { get; set; } // int
[Column, NotNull ] public int DirectoryID { get; set; } // int
[Column, NotNull ] public string Name { get; set; } // varchar(max)
#region Associations
/// <summary>
/// FK_File_Directory
/// </summary>
[Association(ThisKey="DirectoryID", OtherKey="ID", CanBeNull=false, KeyName="FK_File_Directory", BackReferenceName="Files")]
public Directory Directory { get; set; }
#endregion
}
There are now several ways to download directories with their files. Here are a few:
[Test] // 1 query to load directories + separate queries to load files for each directory
public void Test()
{
LinqToDB.Common.Configuration.Linq.AllowMultipleQuery = true;
using(var db = new TestDb())
{
var directoriesWithFiles = db.Directories.LoadWith(d => d.Files).ToList();
}
}
[Test] // same as above, but manually
public void Test2()
{
using(var db = new TestDb())
{
var directories = db.Directories.ToList();
foreach (var d in directories)
{
d.Files = db.Files.Where(f => f.DirectoryID == d.ID).ToList();
}
}
}
[Test] // if you want only 2 queries to the database
public void Test3()
{
using (var db = new TestDb())
{
var dict = new Dictionary<int, List<File>>();
foreach(var file in db.Files)
{
if(!dict.ContainsKey(file.DirectoryID))
dict.Add(file.DirectoryID, new List<File> { file });
else
dict[file.DirectoryID].Add(file);
}
var directories = db.Directories.ToList();
foreach (var d in directories)
{
List<File> files;
d.Files = dict.TryGetValue(d.ID, out files) ? files : new List<File>();
}
}
}
Or you can just create a connection, load everything in 1 request, and then manually connect files to directories in memory. You can write your own extension method to simplify this.
source to share
The Linq join syntax is pretty tricky to wrap around ... I usually use a little trick to only use the "items" variable you created as an intermediate result:
var query = from d in db.Direcories
join f in db.Files on d.Id equals f.DirectoryId into items
from f in items.DefaultIfEmpty()
group f by d into g
select new { Directory = g.Key, Files = g /* optionally add .ToList() or .ToEnumerable() */ };
var result = query.ToList();
Although I suspect there are easier ways to accomplish what you are trying to do using db.Directories.Include(d => d.Files)
or a similar construct, as long as you have the correct relational properties.
source to share