0

I have some tables User, UserSite, Site & Company. UserSite being the many to many relationship and Site holding a CompanyId.

I'm having an issue creating a Linq query to select Users with their sites and company details, the part that makes this complicated is that there is a lot of filtering going on. You are able to filter by User, Company and site on the web page.

When searching by site it makes sense to show that site in the results. However if searching by user I just want to take the first site found for that user therefore removing duplicate rows. In T-SQL I'd do a sub join and take the top 1, no problem. However with Linq I am struggling to do this. So much so I am thinking of going back to Store Procs.

The code below brings back multiple rows for the user due to the site join, how can I bring back one row for the user when filtering by site.

var users = from u in _arcContext.Users
join us in _arcContext.UserSites on u.Id equals us.UserId
join s in _arcContext.Sites on us.SiteId equals s.Id
join c in _arcContext.Companies on s.CompanyId equals c.Id
where 
    (!userSearchParams.UserId.HasValue || u.Id == userSearchParams.UserId) &&
    ((String.IsNullOrEmpty(userSearchParams.UserText) || userSearchParams.UserId.HasValue) || // User Text
        (u.FirstName.StartsWith(userSearchParams.UserText, StringComparison.InvariantCultureIgnoreCase)
            || u.LastName.StartsWith(userSearchParams.UserText, StringComparison.InvariantCultureIgnoreCase)
            || u.Username.StartsWith(userSearchParams.UserText, StringComparison.InvariantCultureIgnoreCase))
        ) &&
    ((!userSearchParams.CompanyId.HasValue || c.Id == userSearchParams.CompanyId)) && // Company Id
    ((String.IsNullOrEmpty(userSearchParams.CompanyText) || userSearchParams.CompanyId.HasValue) || // Company Text
        (c.Name.StartsWith(userSearchParams.CompanyText, StringComparison.InvariantCultureIgnoreCase)
            || c.Country.StartsWith(userSearchParams.CompanyText, StringComparison.InvariantCultureIgnoreCase)
            || c.City.StartsWith(userSearchParams.CompanyText, StringComparison.InvariantCultureIgnoreCase))
        ) &&
    ((!userSearchParams.SiteId.HasValue || s.Id == userSearchParams.SiteId)) && // Site Id
    ((String.IsNullOrEmpty(userSearchParams.SiteText) || userSearchParams.SiteId.HasValue) || // Site Text
        (s.Name.StartsWith(userSearchParams.SiteText, StringComparison.InvariantCultureIgnoreCase)
            || s.Country.StartsWith(userSearchParams.SiteText, StringComparison.InvariantCultureIgnoreCase)
            || s.City.StartsWith(userSearchParams.SiteText, StringComparison.InvariantCultureIgnoreCase)) 
        )
select new UserToSiteAndCompany()
{
User = u,
Site = s,
Company = c
};

Update 1

 var site = (from u in _arcContext.Users
            join us in _arcContext.UserSites on u.Id equals us.UserId
            join s in _arcContext.Sites on us.SiteId equals s.Id
            where
                ((userSearchParams.SiteId.HasValue ? s.Id == userSearchParams.SiteId : true)) && // Site Id
                ((String.IsNullOrEmpty(userSearchParams.SiteText) || userSearchParams.SiteId.HasValue) || // Site Text
                    (s.Name.StartsWith(userSearchParams.SiteText, StringComparison.InvariantCultureIgnoreCase)
                        || s.Country.StartsWith(userSearchParams.SiteText, StringComparison.InvariantCultureIgnoreCase)
                        || s.City.StartsWith(userSearchParams.SiteText, StringComparison.InvariantCultureIgnoreCase)) 
                )
            select new { UserSite = us, Site = s}).Take(1);

            var users = from u in _arcContext.Users
            join us in _arcContext.UserSites on u.Id equals us.UserId
            join s in site on us.SiteId equals s.Site.Id 
            join c in _arcContext.Companies on s.Site.CompanyId equals c.Id
            where 
                (userSearchParams.UserId.HasValue ? u.Id == userSearchParams.UserId : true) &&
                ((String.IsNullOrEmpty(userSearchParams.UserText) || userSearchParams.UserId.HasValue) || // User Text
                    (u.FirstName.StartsWith(userSearchParams.UserText, StringComparison.InvariantCultureIgnoreCase)
                        || u.LastName.StartsWith(userSearchParams.UserText, StringComparison.InvariantCultureIgnoreCase)
                        || u.Username.StartsWith(userSearchParams.UserText, StringComparison.InvariantCultureIgnoreCase))
                    ) &&
                ((userSearchParams.CompanyId.HasValue ? c.Id == userSearchParams.CompanyId : true)) && // Company Id
                ((String.IsNullOrEmpty(userSearchParams.CompanyText) || userSearchParams.CompanyId.HasValue) || // Company Text
                    (c.Name.StartsWith(userSearchParams.CompanyText, StringComparison.InvariantCultureIgnoreCase)
                        || c.Country.StartsWith(userSearchParams.CompanyText, StringComparison.InvariantCultureIgnoreCase)
                        || c.City.StartsWith(userSearchParams.CompanyText, StringComparison.InvariantCultureIgnoreCase))
                    ) 
            select new UserToSiteAndCompany()
            {
            User = u,
            Site = s.Site,
            Company = c
            };
TheEnglishMan_
  • 33
  • 10
  • There's nothing wrong with stored procedures. Use the technology that's most appropriate for the problem. However you can use a .Take(1) in linq as a way to do a TOP 1 in SQL. Finally I recommend simplifying the query by using the null operator and a simple function to replace the whole .StartsWith() function. – Carlo Bos Jun 11 '19 at 20:25
  • Thanks for the reply. How can I .Take(1) site though? I don't want to take just one user from the whole query, I'd like multiple users with a single site. – TheEnglishMan_ Jun 11 '19 at 20:41
  • 1
    Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you? – NetMage Jun 11 '19 at 21:28
  • See edit update @CarloBos I have added the null operators and used Take(1). The issue is that it will only take 1 site. So if the other users are not at that site then we will lose them. – TheEnglishMan_ Jun 13 '19 at 20:37
  • @NetMage I have looked at your recipe and it is useful, thanks. I translated the sub select I wanted into a variable as suggested, however I will still have the issue mentioned above where only users matching the first taken site will ever be shown. I'd like to return multiple users with THEIR first found site returned. Any ideas how this can be accomplished with LINQ? – TheEnglishMan_ Jun 13 '19 at 20:39

0 Answers0