Entity Framework right join to fill user settings

Question

Not sure how to describe this question.

Here is the example:

I have an entity as Tour. Tour table contains static 3 rows(can be more in the future).

     public class Tour
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Location { get; set; }
}

Each user can turn this options keys.

So I have another table called UserTourSetting to store user options.

     public class UserTourSetting
{

    public int Id { get; set; }
    public int UserId { get; set; }
    public int TourId { get; set; }
    public bool Enabled { get; set; }
    public ApplicationUser User { get; set; }
    public Tour Tour { get; set; }
}

When the user loads the page it should always see 3 rows with the status on or off.

When I load UserTourSetting table I get no results as there is no record yet on the table for the user first time. I guess I need to do join with Tour table and also include Tour results as I will need the titles.

When the switches are turned on off then individually it will add a new record if not exists or change Enabled key if exists.

What would be the best way to implement this with EF? I struggle the generate linq statement to load the join table.

here is the method I tried:

public IEnumerable<UserTourSetting> GetUserTourSettings(int userId)
    {
        var q = from tour in DbContext.Tours
                join uts in DbContext.UserTourSettings
                on tour.Id equals uts.TourId
                where uts.UserId == userId
                select uts;
        return q;

    }

But it does not work. And I cannot access Tour names. Where the include can go in this query?

Thanks,

What do you mean by _it does not work_? Method does not return values it should or you can't access Tour.Name. For me it should work: `var firstUserTourSettingsName = GetUserTourSettings(1).First().Tour.Name` — Pablo notPicasso, Jun 29 '17 at 09:03
SELECT [Extent1].[Id] AS [Id], [Extent1].[UserId] AS [UserId], [Extent1].[TourId] AS [TourId], [Extent1].[Enabled] AS [Enabled] FROM [dbo].[UserTourSettings] AS [Extent1] WHERE [Extent1].[UserId] = @p__linq__0 this is genereted script — akd, Jun 29 '17 at 09:26
Generated script is one thing, but data you need is different. Did you even try getting Name from `UserTourSetting.Tour.Name`? — Pablo notPicasso, Jun 29 '17 at 09:46
well clearly generated query would never return anything from Tour as it is not in the query. So the query should return a join table. All rows from Tours table joined results from UserTourSettings. The query result must always return the exact number of rows as in Tours table. No more no less. here is the query I am after SELECT * FROM [Tours] t LEFT JOIN [dbo].[UserTourSettings] uts ON t.Id=uts.TourId WHERE uts.UserId=3 OR uts.UserId is null — akd, Jun 29 '17 at 09:59
But your method returns `IEnumerable` so why do you expect anything else than fields from `UserTourSetting` to be returned? — Pablo notPicasso, Jun 29 '17 at 11:21
Well if I was certain about the implementation I wouldn't ask. The question is what is the solution when you come across issues as above. You have list of settings in a table(tours) and on user settings page user will see all settings with a switch. How would you get that page model With EF? — akd, Jun 29 '17 at 12:41
Please provide code how you plan to use this method `GetUserTourSettings` — Pablo notPicasso, Jun 29 '17 at 13:12

score 1 · Answer 1 · answered Jun 29 '17 at 09:19

1

Please try to set virtual on those properties.

public virtual ApplicationUser User { get; set; }
public virtual  Tour Tour { get; set; }

p/s: for more information, you can see this https://stackoverflow.com/a/8542879/5771430

answered Jun 29 '17 at 09:19

Danny Trương

56
7

Entity Framework right join to fill user settings

1 Answers1