Linq strangeness

I was receiving repeating rows from this linq query:

             public static Func<DataContext, string, IQuery开发者_JS百科able<Building>>
            GearFilteredBuildings =
                CompiledQuery.Compile((DataContext db, string filter) =>
                    from b in db.Building 
                        join r in db.Router on b equals r.Building
                    orderby !b.Active
                    where filter.Length == 5 && r.Name.Substring(1, 5).ToLower() == filter
                        || filter.Substring(0, 3) == r.Name.Substring(3, 3).ToLower()
                    select b);

After some fiddling, I got the distinct Buildings with this:

        public static Func<DataContext, string, IQueryable<Building>>
            GearFilteredBuildings =
                CompiledQuery.Compile((DataContext db, string filter) =>
                    (from b in db.Building
                     join r in db.Router on b equals r.Building
                     orderby !b.Active
                     where filter.Length == 5 && r.Name.Substring(1, 5).ToLower() == filter
                         || filter.Substring(0, 3) == r.Name.Substring(3, 3).ToLower()
                     group b by b.Id into g
                         select g) as IQueryable<Building>); 

Is this an acceptable solution? How else might this be done?

Not sure (couldn't test it IDE) but select...join...lalala can replaced with Linq-Chain syntax:

db.Routers
   .Where(r => filter.Length == 5 && r.Name.Substring(1, 5).ToLower() == filter || filter.Substring(0, 3) == r.Name.Substring(3, 3).ToLower())
   .GroupBy(r => r.Building)
   .Select(g => g.Key)
   .OrderBy(b => !b.Active)

Also: as I can see no joins are really requred in your query, as you have navigation properties (r.Building)in your model.

Or another approach could be used, select all needed buildings, and use .Distinct() afterwards:

db.Routers
    .Where (r => filter.Length == 5 && r.Name.Substring(1, 5).ToLower() == filter || filter.Substring(0, 3) == r.Name.Substring(3, 3).ToLower())
    .Select(r => r.Building)
    .Distinct()
    .OrderBy(b => !b.Active)