1

I am newbie @ LINQ and trying my best. I know how to pull and join these tables in a one-to-one relationship, but I am stuck trying to implement this, with a better approach:

  1. One-to-One
  2. One-to-Many

But now I have no idea how to get the Player and Scores but from a specific Datetime in the Game table. I am using: .NET Framework 4.7.2 and Entity Framework 6.4.4, and working with Code First approach.

EDIT: I normally join them on the matching Datetime timestamp.

EDIT #2: My attempt: ....=> t -> is some input to this function, below is the body:

var data = (from game in db.Games
        join p in db.Players on game.Players equals p
        join s in db.Scores on p equals s.Player
        where 
        (
            game.timestamp.Hour == t.Hour &&
            game.timestamp.Minute == t.Minute &&
            game.timestamp.Second == t.Second
        )
        select new { s, p }).ToList<Object>();

EDIT #3: Trying to get this into List<Object>().

EDIT #4: When I try to loop over the data, after returning it, using the Query sample from @Svyatoslav Danyliv's answer, I get this:

enter image description here

My tables:

using System;
using System.Collections.Generic;

namespace Sample
{
    ////////////////////////////////////////
    //
    // Game and Player have a one-to-many relationship
    // Scores and Player have a one-to-one relationship
    //
    
    public class Player
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int PlayerID { get; set; }

        [MaxLength(200)]
        public string Name { get; set; }
        
        [DataType(DataType.DateTime)]
        public DateTime Created { get; set; };
    }
    
    public class Scores
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ScoreID { get; set; }
        
        public int Wins { get; set; } = 0;
        public int Lost { get; set; } = 0;
        public int TimesPlayed { get; set; } = 0;

        [Required]
        public virtual Player Player { get; set; }
        
        [DataType(DataType.DateTime)]
        public DateTime timestamp { get; set; };
    }
    
    public class Game
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }
        
        public DateTime timestamp { get; set; };
        
        public virtual ICollection<Player> Players { get; set; } = new List<Player>();
    }
}

2 Answers2

0

You appear to be missing a navigation property from Player to Score, which also seems like an odd design, since I would normally assume that a player could play multiple games, and hence could have multiple scores. However, if you add the missing navigation property, then should work:

var data = db.Games
  .Include(g=>g.Players)
  .Include(g=>g.Players.Select(p=>p.Scores)) // EF 6.x
  //.ThenInclude(p=>p.Scores) // EF Core
  .Where(g=>g.timestamp.Hour == t.Hour)
  .Where(g=>g.timestamp.Minute == t.Minute)
  .Where(g=>g.timestamp.Second == t.Second)
  .ToList();

There is probably a much better way of handling your game timestamp as well using DbFunctions or EntityFunctions like from here: Linq To Sql compare Time only

If you wanted this flattened, then project using SelectMany:

var data = db.Games
  .Include(g=>g.Players)
  .Include(g=>g.Players.Select(p=>p.Scores)) // EF 6.x
  //.ThenInclude(p=>p.Scores) // EF Core
  .Where(g=>g.timestamp.Hour == t.Hour)
  .Where(g=>g.timestamp.Minute == t.Minute)
  .Where(g=>g.timestamp.Second == t.Second)
  .SelectMany(g=>g.Players)
  .ToList();
Robert McKee
  • 21,305
  • 1
  • 43
  • 57
  • Hi Robert, this design is meant to store "history" records only, so, new players can play. After a game everything is store. Not meant to have single accounts or anything like that. Only History records. –  Dec 24 '20 at 21:51
0

Something like that, but I think you have to apply appropriate filter on Score's timestamp

var query = 
   from game in db.Games
   where 
   (
      game.timestamp.Hour == t.Hour &&
      game.timestamp.Minute == t.Minute &&
      game.timestamp.Second == t.Second
   )
   select new 
   { 
      Game = game,
      Players = game.Players.Select(p => new 
      {
         Player = p,
         Scores = db.Scores.Where(s = s.Player == p).ToArray()
      }).ToArray()
   }

var result = query.ToList();
Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32
  • Seems cool, but I cannot get it to work with `List` –  Dec 25 '20 at 13:44
  • Because I want to loop each object? And access as **properties**, or that's not possible? –  Dec 25 '20 at 15:40
  • 1
    Original question has no information about this. Update. question and I'll check possible this or not. – Svyatoslav Danyliv Dec 25 '20 at 15:54
  • Updated now. Maybe this is clearer now. I just tried it, but, I an not sure. I get **AnonymousType**. –  Dec 25 '20 at 16:23
  • I didn't get from your update for which purpose you need that. Bu transferring to List is simple: `query.AsEmumerable().Cast().ToList()` – Svyatoslav Danyliv Dec 25 '20 at 16:29
  • Well, I solved it. Very differently, and it seems to work perfectly well. –  Dec 25 '20 at 17:16