Requete Linq avec jointure [Débutant(e)]

[zizou100]

Bonjour,
Je suis nouveau en Linq et je n'arrive pas à transformer ma requête SQL initiale.
Du coup je vous mets la solution ultra lente et ultra non-optimisée pour que vous compreniez bien ce que j essaye de faire.

Le but c'est de sélectionner tous les matches de ma table Matches ou j'ai un certain Player présent soit en tant que Player1 ou en tant que Player2.

Table Matches(IdPlayer1, IdPlayer2, Date, ..)

Table Ratings(IdPlayer, DateRanking, Position)

Donc la liste des matches récupérées contiendra ce joueur soit dans le champ player1 ou dans le champ Player2 et son adversaire dans le champ opposé.

Code c# :

1
2
3
4
5
6
7
MatchesCollection _listMatchesForTournament = new MatchesCollection();
            DateTime _dateDebut = (DateTime.Today).AddYears(-3).AddMonths(-1);// -(new DateTime(3, 0, 10));
            var result = (from matches in Db.TableMatches
                          where (matches.Id1 == aIdPlayer || matches.Id2 == aIdPlayer) 
                            && (matches.Date >= _dateDebut) 
                          orderby matches.Date descending
                          select matches);

Ensuite j'ai donc une table Ratings qui contient le classement de chaque joueur pour chaque semaine de l année.
Pour chacun des 2 joueurs de chaque match sélectionné j aurais donc souhaité pouvoir récupérer son classement . Ce que je fais donc(car je n arrive pas a tout faire dans la meme requete) en parcourant ma liste de matches récupérés par la 1e requete et pour chaque match je vais chercher dans ma table Rating par r1 et r2.

Code c# :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
//
foreach (var matches in result)
            {    
                var r1 = (from ratings in matches.Player1.RatingsForPlayer
                         where ratings.DateRanking == matches.Tournament.Date
                        select ratings.Position).SingleOrDefault();
                var r2 = (from ratings in matches.Player2.RatingsForPlayer
                          where ratings.DateRanking == matches.Tournament.Date
                          select ratings.Position).SingleOrDefault();
                _listMatchesForTournament.Add(new Match
                {
                    Date = matches.Date,
                    Player1 = matches.Player1,
                    Player2 = matches.Player2,
                    Tournament = matches.Tournament,
                    Round = matches.Round,
                    ResultString = matches.ResultString,
                    PositionPlayer1 = r1,
                    PositionPlayer2 = r2
                }
                );
            }

J'espère que c'est clair et merci par avance.

[tomlev]

Est-ce que ça correspond à ce que tu veux ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
from match in Db.TableMatches
where (match.Id1 == aIdPlayer || match.Id2 == aIdPlayer)
&& match.Date >= _dateDebut
let r1 = match.Player1.Ratings.Where(r => r.DateRanking == match.Date).SingleOrDefault()
let r2 = match.Player2.Ratings.Where(r => r.DateRanking == match.Date).SingleOrDefault()
select new Match
{
    Date = match.Date,
    Player1 = match.Player1,
    Player2 = match.Player2,
    Tournament = match.Tournament,
    Round = match.Round,
    ResultString = match.ResultString,
    PositionPlayer1 = r1.Position,
    PositionPlayer2 = r2.Position
};

Sinon, montre ta requête SQL d'origine, elle peut peut-être se traduire directement en Linq

[youtpout978]

edit:grilled
bien joué tomlev belle requête

[zizou100]

Merci de l aide.
Alors now j ai le message suivant

{"La construction explicite d'un type Entity '[maBase].Match' dans la requête n'est pas autorisé."}

c'est à cause du "select new Match" car si je mets "select match" ça passe mais bon du coup je fais comment pour récupérer les valeurs de r1 et r2?


Merci pour la mise en page et précision je travaille sur une base MS Access mais bon ça m'a pas posé de soucis jusque là notamment pour faire la requête en 2 temps. Enfin ça veut dire que j'ai fait mes classes à la main par contre. Je fais que de la lecture sur ma base par ailleurs.

PS non je peux pas migrer ma base access^^

[zizou100]

Ah j'ai une question pour faire le "select new Match" dois je avoir un constructeur qui correspond avec tous les arguments dans ma class Match() car a l heure actuelle je n ai qu un constructeur vide c'est peut etre le soucis?
Merci

[tomlev]

Euh... vu la tête de la requête, finalement il vaut peut-être mieux pas essayer de la traduire

Tu utilises Access ? Et comment tu fais du Linq sur Access au juste ? Les providers fournis avec le framework ne le permettent pas, tu utilises un connecteur tiers ?

Sinon, ton type OnCourtData.Match est défini dans le modèle objet de la DB ? Si c'est le cas, tu ne peux pas en créer une instance directement dans la requête, normalement tu le récupères directement à partir d'une table.

Tu peux toujours passer par un type anonyme :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
var query =
    from match in Db.TableMatches
    where (match.Id1 == aIdPlayer || match.Id2 == aIdPlayer)
    && match.Date >= _dateDebut
    let r1 = match.Player1.Ratings.Where(r => r.DateRanking == match.Date).SingleOrDefault()
    let r2 = match.Player2.Ratings.Where(r => r.DateRanking == match.Date).SingleOrDefault()
    select new
    {
        Date = match.Date,
        Player1 = match.Player1,
        Player2 = match.Player2,
        Tournament = match.Tournament,
        Round = match.Round,
        ResultString = match.ResultString,
        PositionPlayer1 = r1.Position,
        PositionPlayer2 = r2.Position
    };

Et ensuite tu le mappes sur le type Match, mais avec Linq to Objects :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
var matches =
    from result in query.AsEnumerable()
    select new Match
    {
        Date = result.Date,
        Player1 = result.Player1,
        Player2 = result.Player2,
        Tournament = result.Tournament,
        Round = result.Round,
        ResultString = result.ResultString,
        PositionPlayer1 = result.Position,
        PositionPlayer2 = result.Position
    };

(le AsEnumerable() permet de transformer un IQueryable en IEnumerable, pour travailler en Linq to Objects)

[zizou100]

J utilise ca en fait pour me connecter à Access, ça pose pas de soucis en lecture. Par contre en update insert il faut bidouiller plus apparemment...

Code c# :

1
2
3
string _connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[Path]\\file.mdb;User ID=;Jet OLEDB:Database Password=xxxx;Persist Security Info=true;";
            IDbConnection _connectionDb = new System.Data.OleDb.OleDbConnection(_connectionString);
            Db = new myDb(_connectionDb);

avec myDb qui hérite DataContext.

Maintenant j ai l erreur suivante

{"Erreur de syntaxe (opérateur absent) dans l'expression '[t1].[ID_P] = [t0].[ID1_G]\r\nINNER JOIN [Players] AS [t2] ON [t2].[ID_P] = [t0].[ID2_G]\r\nINNER JOIN [Tours] AS [t3] ON [t3].[ID_T] = [t0].[ID_T_G]\r\nINNER JOIN [Rounds] AS [t4] ON [t4].[ID_R] = [t0].[ID_R_G]'."}

Je me demande si y a pas un soucis entre le let et access du coup.

[zizou100]

Bon je vais mettre le détail de qqs propriétés pour voir si y a pas un soucis

classe Match

Code c# :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
	private EntityRef<Player> link_Player1 = new EntityRef<Player>();
        [Association(IsForeignKey = true, Storage = "link_Player1", ThisKey = "Id1")]
        public Player Player1
        {
            get
            {
                return this.link_Player1.Entity;
            }
            set
            {
                this.link_Player1.Entity = value;
            }
        }
 
        private EntityRef<Player> link_Player2 = new EntityRef<Player>();
        [Association(IsForeignKey = true, Storage = "link_Player2", ThisKey = "Id2")]
        public Player Player2
        {
            get
            {
                return this.link_Player2.Entity;
            }
            set
            {
                this.link_Player2.Entity = value;
            }
        }

classe Player

Code c# :

1
2
3
4
5
6
7
8
9
10
 
 
	private EntitySet<Ratings> link_Ratings = new EntitySet<Ratings>();
        [Association(Storage = "link_Ratings", OtherKey = "PlayerId"
            , ThisKey = "Id")]
        public ICollection<Ratings> RatingsForPlayer 
        {
            get { return this.link_Ratings; }
            set { this.link_Ratings.Assign(value); } 
        }

classe Ratings

Code c# :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
	[Table(Name = "Ratings")]
    public class Ratings
    {
        [Column(Name = "DATE_R", IsPrimaryKey = true)]
        public DateTime? DateRanking { get; set; }
 
        [Column(Name = "ID_P_R", IsPrimaryKey = true)]
        public int PlayerId { get; set; }
 
        [Column(Name = "POS_R", DbType = "smallint")]
        public int Position { get; set; }
 
        public override string ToString()
        {
            return Position.ToString();
        }
    }

J'espère que c'est solvable j'ai passé pas mal d'heures à essayer de migrer ça sous Linq2Sql mais la je suis coincé à moins de faire ma requête en 2 temps mais du coup c'est super lent. Et sinon tout le reste de mon appli marche y a juste cette requête un peu plus complexe...

[zizou100]

Bon en fait j'ai isolé le problème

Quand je fais ca j ai le message d erreur au moment de parcourir le resultat de la requete avec un foreach(des le debut de la boucle)

Code c# :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
from match in Db.TableMatches
where (match.Id1 == aIdPlayer || match.Id2 == aIdPlayer)
&& match.Date >= _dateDebut
select new 
{
    Date = match.Date,
    Player1 = match.Player1,
    Player2 = match.Player2,
    Tournament = match.Tournament,
    Round = match.Round,
    ResultString = match.ResultString,
    PositionPlayer1 = 1,
    PositionPlayer2 = 1
};
foreach (var match in query)
            {
                _listMatchesForTournament.Add(new Match
                {
                    Date = match.Date,
                    Player1 = match.Player1,
                    Player2 = match.Player2,
                    Tournament = match.Tournament,
                    Round = match.Round,
                    ResultString = match.ResultString,
                    PositionPlayer1 = match.PositionPlayer1,
                    PositionPlayer2 = match.PositionPlayer2
                }
                );
            }

mais qand je fais ca j ai pas le message d erreur

Code c# :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
from match in Db.TableMatches
where (match.Id1 == aIdPlayer || match.Id2 == aIdPlayer)
&& match.Date >= _dateDebut
select match;
foreach (var match in query)
            {
                _listMatchesForTournament.Add(new Match
                {
                    Date = match.Date,
                    Player1 = match.Player1,
                    Player2 = match.Player2,
                    Tournament = match.Tournament,
                    Round = match.Round,
                    ResultString = match.ResultString,
                    PositionPlayer1 = match.PositionPlayer1,
                    PositionPlayer2 = match.PositionPlayer2
                }
                );
            }

Je comprends pas pourquoi.
il semble que ce qui bloque dans l anonymous c'est ça
Player1 = match.Player1,
Player2 = match.Player2,
Tournament = match.Tournament,
Round = match.Round,
c'est à dire mes 4 propriétés qui sont des instances de classe, quand c'est pour une date ou un string ça passe.

[buggen25]

Bonjour,
Pour le premier cas c'est tout a fait normal qu'il y est une erreur pusique dans le code suivant :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
from match in Db.TableMatches
where (match.Id1 == aIdPlayer || match.Id2 == aIdPlayer)
&& match.Date >= _dateDebut
select new 
{
    Date = match.Date,
    Player1 = match.Player1,
    Player2 = match.Player2,
    Tournament = match.Tournament,
    Round = match.Round,
    ResultString = match.ResultString,
    PositionPlayer1 = 1,
    PositionPlayer2 = 1
};

Tu es entrain d'instancer l'occurence d'un nouvel objet alors qu'il faut selectionner un "match" parmis ceux de la requete

Dans le second code, c'est normal que sa fontionne puisque tu parcours le jeu de resultat dans "query", et pour chaque objet de la requete, tu crée un nouvel element que tu insere dans la liste des matches
Cordialement

[zizou100]

Ok merci mais alors si j insère ces 2 lignes

let r1 = match.Player1.Ratings.Where(r => r.DateRanking == match.Date).SingleOrDefault()
let r2 = match.Player2.Ratings.Where(r => r.DateRanking == match.Date).SingleOrDefault()

comment je fais pour récupérer leurs valeurs en plus des valeurs des différents attributs de match?

[tomlev]

Ce que tu fais n'est pas une utilisation prévue de Linq to SQL...

Linq to SQL est prévu pour SQL Server et SQL Server Compact, et c'est tout ! Ce n'est absolument pas fait pour s'utiliser avec Access ou quoi que ce soit d'autre. Ca peut marcher dans certains cas (tant que les requêtes SQL générées sont compatibles), mais dans certains cas ça va générer des requêtes qui utilisent des fonctions propres à SQL Server, et ça ne fonctionnera plus. L'erreur de syntaxe que tu obtiens en est l'illustration...

Tu vas peut-être pouvoir t'en sortir tant que tu fais des requêtes très simples, mais sur le long terme cette solution n'est pas viable. Si tu veux absolument utiliser un ORM avec Access, le seul qui soit compatible (à ma connaissance) est NHibernate.

[buggen25]

Envoyé par tomlev

Ce que tu fais n'est pas une utilisation prévue de Linq to SQL...

Linq to SQL est prévu pour SQL Server et SQL Server Compact, et c'est tout ! Ce n'est absolument pas fait pour s'utiliser avec Access ou quoi que ce soit d'autre. Ca peut marcher dans certains cas (tant que les requêtes SQL générées sont compatibles), mais dans certains cas ça va générer des requêtes qui utilisent des fonctions propres à SQL Server, et ça ne fonctionnera plus. L'erreur de syntaxe que tu obtiens en est l'illustration...

Tu vas peut-être pouvoir t'en sortir tant que tu fais des requêtes très simples, mais sur le long terme cette solution n'est pas viable. Si tu veux absolument utiliser un ORM avec Access, le seul qui soit compatible (à ma connaissance) est NHibernate.

Je rajoute, Linq2SQL permet de traduire n'importe quelle requete SQL Server en requete LINQ. Comme on peut aussi utiliser EntityFramework qui permet meme d'utiliser directement des procedures stockées SQLServer
Cordialement,

[tomlev]

Envoyé par buggen25

Je rajoute, Linq2SQL permet de traduire n'importe quelle requete SQL Server en requete LINQ.

Ah bon ? C'est nouveau ça...
Linq to SQL permet de convertir une requête Linq en SQL (à condition de n'utiliser que des fonctions qui ont un équivalent en SQL), mais le contraire n'est absolument pas vrai....

[buggen25]

Envoyé par tomlev

Ah bon ? C'est nouveau ça...
Linq to SQL permet de convertir une requête Linq en SQL (à condition de n'utiliser que des fonctions qui ont un équivalent en SQL), mais le contraire n'est absolument pas vrai....

Merci pour la precision. c'est pour ça qu'on s'arrache parfois les cheveux qund on essaye de traduire une requete SQL en linqToSQL, Mais entity framework, c'est cool pusiqu'on peut utiliser les procedures stockée, c'est ce que je fais toujours puisque ça m'evite de recompiler mon code source...

Encore merci pour la precision,
Cordialement

[tomlev]

Envoyé par buggen25

Mais entity framework, c'est cool pusiqu'on peut utiliser les procedures stockée

Si j'ai bonne mémoire, on peut aussi le faire avec Linq to SQL (mais je peux me tromper, j'ai très peu utilisé Linq to SQL...)

[zizou100]

Merci pour les différentes réponses