Linq-to-sql many-many in database first

Question

I have a database with 2 table Song vs Artist mapping by Mapping_Artist_Song, then i add existing database to models(database first) in my project and now i dont know how to use linq to select song have same id but difference artist in database :

Song:             

SongID| SongName
------------------ 
|1      |      A |
|2      |      B |
|3      |      C |
------------------
Artist 
Artist ID| Artist Name
------------------ 
|1      |      D |
|2      |      E |
|3      |      F |
------------------
Mapping by :

MAPPING_Artist _SONG
SongID| Artist ID
------------------ 
|1      |      1 |
|1      |      2 |
|1      |      3 |
|3      |      2 |
------------------

Any help really appreciated

Thanks

score 2 · Accepted Answer · edited Jun 20 '17 at 06:39

2

For the person who find the solution like me :

var a = from b in BusinessModels.Songs     
   where b.SongName.Contains("") 
   from c in b.Artists
   select c;

edited Jun 20 '17 at 06:39

Rob

26,989
16
82
98

answered Jun 20 '17 at 02:35

Tan Sang

1,897
1
16
28

score 0 · Answer 2 · answered Jun 19 '17 at 12:36

0

Question is not clear, but I think that you are looking for GroupBy method. Then you can count how many artists are connected to specyfic song.

answered Jun 19 '17 at 12:36

Thowk

386
4
15

score 0 · Answer 3 · answered Jun 19 '17 at 13:06

See code below

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication63
{
    class Program
    {

        static void Main(string[] args)
        {
            Song.songs = new List<Song>() {
                new Song() { SongID = 1, SongName = "A"},
                new Song() { SongID = 2, SongName = "B"},
                new Song() { SongID = 3, SongName = "C"}
            };

            Artist.artists = new List<Artist>() {
                new Artist() { ArtistID= 1, ArtistName= "D"},
                new Artist() { ArtistID= 2, ArtistName= "E"},
                new Artist() { ArtistID= 3, ArtistName= "F"}
            };

            MAPPING_Artist.mappings = new List<MAPPING_Artist>() {
             new MAPPING_Artist() { SongID = 1, ArtistID = 1},
             new MAPPING_Artist() { SongID = 1, ArtistID = 2},
             new MAPPING_Artist() { SongID = 1, ArtistID = 3},
             new MAPPING_Artist() { SongID = 3, ArtistID = 2}
            };


            var groups = (from map in MAPPING_Artist.mappings
                          join song in Song.songs on map.SongID equals song.SongID
                          join art in Artist.artists on map.ArtistID equals art.ArtistID 
                          select new { songID = song.SongID,songName = song.SongName, artistname = art.ArtistName})
                          .GroupBy(x => x.songID)
                          .Select(x => new {
                              songID = x.Key,
                              songName = x.FirstOrDefault().songName,
                              artists = x.Select(y => y.artistname).ToList()
                          }).ToList();
        }
    }
    public class Song
    {
        public static List<Song> songs = new List<Song>();
        public int SongID { get; set; }
        public string SongName { get; set; }
    }
    public class Artist
    {
        public static List<Artist> artists = new List<Artist>();
        public int ArtistID { get; set; }
        public string ArtistName { get; set; }
    }
    public class MAPPING_Artist
    {
        public static List<MAPPING_Artist> mappings = new List<MAPPING_Artist>();
        public int SongID { get; set; }
        public int ArtistID { get; set; }
    }
}

Although this code might answer the question, one should always explain what it does and how it solves the problem. — BDL, Jun 19 '17 at 14:08
The person new how to use Linq from question. Wasn't sure of syntax. — jdweng, Jun 19 '17 at 14:23
The asker might know, but SO is a knowledge base so answers should be written in a way that also other users with the same problem can benefit from it. — BDL, Jun 19 '17 at 14:24

Linq-to-sql many-many in database first

3 Answers3