2
\$\begingroup\$

I was looking at some Linq queries with multiple join statements. I was thinking that they could be cleaned up using extension methods that hide away the join / where statements.

For example:

(from myObject in _context.MyObject
join myObjectGroupName in _context.MyObjectGroupNames on myObject.TaskGroupNameKey equals myObjectGroupName.ReportGroupNameKey
join myObjectResourceType in _context.MyObjectResourceTypes on myObject.ResourceKey equals myObjectResourceType.ResourceKey
where myObjectGroupName.ReportGroupName.Trim().ToUpper().Equals(groupName.Trim().ToUpper()) && fileTypes.Contains(myObjectResourceType.ResourceType)
select myObject.PermissionKey);

Now becomes:

_context.MyObject.JoinWhere(_context.MyObjectGroupNames, groupName)
                 .JoinWhere(_context.MyObjectResourceTypes, fileTypes)
                 .Select(x => x.PermissionKey);

Using:

public static IQueryable<MyObject> JoinWhere(this IQueryable<MyObject> initialObject, IQueryable<MyObjectGroupName> customerReportGroupNames, string groupName)
{
    return (from myObject in initialObject
        join myObjectGroupName in customerReportGroupNames on myObject.TaskGroupNameKey equals myObjectGroupName.ReportGroupNameKey
        where myObjectGroupName.ReportGroupName.Trim().ToUpper().Equals(groupName.Trim().ToUpper())
        select myObject);
}

public static IQueryable<MyObject> JoinWhere(this IQueryable<MyObject> initialObject, IQueryable<MyObjectResourceType> myObjectResourceTypes, IEnumerable<string> types)
{
    return (from myObject in initialObject
        join myObjectResourceType in myObjectResourceTypes on myObject.ResourceKey equals myObjectResourceType.ResourceKey
        where types.Contains(myObjectResourceType.ResourceType)
        select myObject);
}

After more consideration I was wondering if it would be possible to make these methods generic:

public static IQueryable<T> JoinWhere(this IQueryable<T> initialObject, IQueryable<TJoinOn> joinOn, IEnumerable<string> types)
{
    return (from myObject in initialObject
                                               //Here                      //Here
        join myObjectResourceType in joinOn on myObject.ResourceKey equals myObjectResourceType.ResourceKey
                             //Here
        where types.Contains(myObjectResourceType.ResourceType)
        select myObject);
}

Where I have put my comments are specific to specific objects. Could those be used generically or would I have to create an extension method for each dataObject making this WAY more work than it's really worth?

\$\endgroup\$
  • 2
    \$\begingroup\$ Not sure if your ORM supports this, but in general you can remove the need for a lot of joins altogther by fixing your entity mappings. ie instead of joining MyObjectGroupNames, you can go "MyObject.GroupNames.Where(x ==x...." for example. \$\endgroup\$ – Mitchell Lee Jul 31 '14 at 15:07
  • \$\begingroup\$ I have been considering doing that. EF allows you to create partial classes in tandem with the generated objects it gives you. I was considering adding relationships to all of the objects. There are over 50 so I was hoping for a simpler solution \$\endgroup\$ – Robert Jul 31 '14 at 16:06
  • \$\begingroup\$ I've been using the Database First, Code Second approach (use Code First with an existing database) for all of my projects, and it works really well. I like how that allows for the abstraction of the POCO classes out of the Repository layer so that you can freely swap in and out any database storage layers and still use the same classes and not have to worry about partials. My worst one was only about 25 though, and it takes MAYBE 45 minutes per table/class to get everything done and running. \$\endgroup\$ – krillgar Jul 31 '14 at 17:39
  • \$\begingroup\$ @MitchellLee I have decided that's what I am going to go with. Could you put your comment as an answer and I will accept it. \$\endgroup\$ – Robert Aug 1 '14 at 12:49
  • \$\begingroup\$ @Robert posted as answer, glad I could help. \$\endgroup\$ – Mitchell Lee Oct 12 '14 at 19:26
1
\$\begingroup\$

Not sure if your ORM supports this, but in general you can remove the need for a lot of joins altogther by fixing your entity mappings.

ie instead of joining MyObjectGroupNames, you can go:

MyObject.GroupNames.Where(x == x....
\$\endgroup\$
1
\$\begingroup\$

Have you considered using in Interface on MyObject?

public static IQueryable<T> JoinWhere(this IQueryable<T> initialObject, IQueryable<TJoinOn> joinOn, IEnumerable<string> types) 
where T : IMyObject
{
    return (from myObject in initialObject
                                               //Here                      //Here
        join myObjectResourceType in joinOn on myObject.ResourceKey equals myObjectResourceType.ResourceKey
                             //Here
        where types.Contains(myObjectResourceType.ResourceType)
        select myObject);
}
\$\endgroup\$
  • \$\begingroup\$ This is good except I would have to do that for all of my different objects from the database. The problem with that is, in its current structure the DB has 50+ tables \$\endgroup\$ – Robert Jul 31 '14 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.