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:
- One-to-One
- 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:
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>();
}
}