CODEWITHSUNDEEP
sql-serverlinqasp.net-coreentity-framework-core
Brokrammer
Brokrammer

Reputation: 33

How can I join two tables in LINQ?

I use ASP.NET Core and EF Core.

I have two tables FileType and UniversityFile.

UniversityFile saves number of each file extensions in some universities. I want to join these two tables and get a list to send to a method which creates an Excel report.

This is an example of the FileType and UniversityFile tables:

{
        List<FileType> FileTypes= new List<FileType>()
        {
            new FileType() { Id = 1, Extention= "Zip" },
            new FileType() { Id = 2, Extention= "MP3" },
            new FileType() { Id = 3, Extention= "Ogg" },
        };

        List<UniversityFile> UniversityFiles= new List<UniversityFile>()
        {
            new UniversityFile() { UniversityId = 1, FileTypeId = 1, Count = 5 },
            new UniversityFile() { UniversityId = 1, FileTypeId = 2, Count = 10  },
            new UniversityFile() { UniversityId = 1, FileTypeId = 3, Count = 5 },
            new UniversityFile() { UniversityId = 2, FileTypeId = 1, Count = 10 },
            new UniversityFile() { UniversityId = 2, FileTypeId = 2, Count = 10 },
            new UniversityFile() { UniversityId = 2, FileTypeId = 3, Count = 10 }
        };
}

and here is the list I need to send to my method

var data = new List<>()
    {
        new { UniversityId = 1, Zip = 5, MP3 = 10, Ogg = 5 },
        new { UniversityId = 2, Zip = 10, MP3 = 10, Ogg = 10  },
    };

I tried Linq Join but I can't find the right way to get the list

Upvotes: 2

Views: 94

Answers (3)

Jan Jurn&#237;ček
Jan Jurn&#237;ček

Reputation: 136

var result = (from uf in UniversityFiles
                      group uf by new { uf.UniversityId } into gr
                      from x in gr
                      select new
                      {
                          UniversityId = x.UniversityId,
                          Zip = gr.Where(y => y.FileTypeId == 1).Sum(y => y.Count),
                          MP3 = gr.Where(y => y.FileTypeId == 2).Sum(y => y.Count),
                          Ogg = gr.Where(y => y.FileTypeId == 3).Sum(y => y.Count)
                      }).Distinct().ToList();

Upvotes: 0

Farshad Delavarpour
Farshad Delavarpour

Reputation: 205

var result = new List<object>();

foreach (var universityFile in universityFiles
    .GroupBy(universityFile => universityFile.UniversityId)) 
     result.Add(new { universityFile.Key, Zip = universityFile
         .Where(universityFile => universityFile.FileTypeId == 1)
             .Sum(universityFile => universityFile.Count), MP3 = universityFile.Where(universityFile => universityFile.FileTypeId == 2)
             .Sum(universityFile => universityFile.Count), Ogg = universityFile.Where(universityFile => universityFile.FileTypeId == 3)
             .Sum(universityFile => universityFile.Count) });

Upvotes: 1

Michal Rosenbaum
Michal Rosenbaum

Reputation: 2061

Try something like this:

var result = context.UniversityFiles
    .GroupBy(x => x.UniversityId)
    .Select(x => new
    {
        UniversityId = x.Key,
        Zip = x.Where(y => y.FileTypeId == 1).Sum(y => y.Count),
        MP3 = x.Where(y => y.FileTypeId == 2).Sum(y => y.Count),
        Ogg = x.Where(y => y.FileTypeId == 3).Sum(y => y.Count)
    })
    .ToList();

Upvotes: 3

Related Questions