Convert to entity framework

Question

I have two tables, results table:

And a events table:

I want to display all the fields from the events table based on this condition: Anytinme Results.EventId duplicates and Results.LikeTrueFalse = True display all fields from the Events table for UserId 147. So for this example, the query result should be this highlighted event:

Answer 1

@Esteban Peralta , based on your description, you want to get the result from Events table by filtering the data from results table.

First, I created the following two tables.

CREATE TABLE [dbo].[Results] (  
    [ResultsId]       INT           NOT NULL,  
    [Name]            NVARCHAR (50) NOT NULL,  
    [UserId]          INT           NOT NULL,  
    [EventId]         INT           NOT NULL,  
    [LikeTrueorFalse] BIT           NOT NULL  
);  
  
CREATE TABLE [dbo].[EventsDetails] (  
    [EventId]     INT           NOT NULL,  
    [Name]        NVARCHAR (50) NOT NULL,  
    [Description] NVARCHAR (50) NOT NULL,  
    [City]        NVARCHAR (50) NOT NULL,  
    [State]       NVARCHAR (50) NOT NULL,  
    [GroupSize]   INT           NOT NULL,  
    [CateGory]    NVARCHAR (50) NOT NULL,  
    [ImageUrl]    NVARCHAR (50) NOT NULL  
);  

Second, I added ADO.NET Entity Data Model to my project to generate the dbcontext and model related to the database.

public partial class ExampleDBEntities : DbContext  
    {  
        public ExampleDBEntities()  
            : base("name=ExampleDBEntities")  
        {  
        }  
      
        protected override void OnModelCreating(DbModelBuilder modelBuilder)  
        {  
            throw new UnintentionalCodeFirstException();  
        }  
      
        public virtual DbSet<EventsDetail> EventsDetails { get; set; }  
        public virtual DbSet<Result> Results { get; set; }  
    }  
  
    public partial class EventsDetail  
    {  
        public int EventId { get; set; }  
        public string Name { get; set; }  
        public string Description { get; set; }  
        public string City { get; set; }  
        public string State { get; set; }  
        public int GroupSize { get; set; }  
        public string CateGory { get; set; }  
        public string ImageUrl { get; set; }  
    }  
  
 public partial class Result  
    {  
        public int ResultsId { get; set; }  
        public string Name { get; set; }  
        public int UserId { get; set; }  
        public int EventId { get; set; }  
        public bool LikeTrueorFalse { get; set; }  
    }  

Finally, I can use the following code to get what you wanted.

    ExampleDBEntities dbcontext=new ExampleDBEntities();  
    var result = dbcontext.Results.ToList().GroupBy(c => c.EventId).Where(g => g.Skip(1).Any()).SelectMany(c => c).Where(i=>i.LikeTrueorFalse==true&&i.UserId==147);  
    int eventid = result.FirstOrDefault().ResultsId;  
    EventsDetail detail = dbcontext.EventsDetails.Where(i => i.EventId == eventid).FirstOrDefault();  

Result:

Hope this could help you.

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.