3

I am pretty new to Entity Framework and LINQ and I have an entity with more than 10+ other associated entities (one-to-many relationships). Now, I'm planning to make a search page in my application in which users could select which fields (i.e. those 10+ tables) they want to be considered when searching.

Now, I'm trying to write a query to achieve the above goal. Any help how I could sort this out using LINQ method syntax? I mean, to write a multiple join query based on user's choice. (i.e. which of Class1, Class2, ... to join with main Entity to finally have all the related fields in one place). Below is a sample code (Just a hunch, in fact)

if(somefilter#1)
result = db.Companies.Join(db.Channels, p => p.Id, k => k.CId,
                                       (p, k) => new {Company = p, Channels=k});
if(somefilter#2)
result = result.Join(db.BusinnessType, ........);

if(somefilter#3)
result = result.Join(db.Values, .......);
Moji
  • 361
  • 6
  • 19

1 Answers1

2

For complex queries it may be easier to use the other LINQ notation. You could join multiple entities like this:

from myEntity in dbContext.MyEntities
join myOtherEntity in dbContext.MyOtherEntities on myEntity.Id equals myOtherEntity.MyEntityId
join oneMoreEntity in dbContext.OneMoreEntities on myEntity.Id equals oneMoreEntity.MyEntityId
select new {
    myEntity.Id,
    myEntity.Name,
    myOtherEntity.OtherProperty,
    oneMoreEntity.OneMoreProperty
}

You can join in other entities by adding more join statements. You can select properties of any entity from your query. The example I provided uses a dynamic class, but you can also define a class (like MyJoinedEntity) into which you can select instead. To do it you would use something like:

...
select new MyJoinedEntity {
    Id = myEntity.Id,
    Name = myEntity.Name,
    OtherProperty = myOtherEntity.OtherProperty,
    OneMoreProperty = oneMoreEntity.OneMoreProperty
}

EDIT:

In case when you want to have conditional joins you can define MyJoinedEntity with all the properties you will need if you were to join everything. Then break up the join into multiple methods. Like this:

public IEnumerable<MyJoinedEntity> GetEntities() {
    var joinedEntities = from myEntity in dbContext.MyEntities
        join myOtherEntity in dbContext.MyOtherEntities on myEntity.Id equals myOtherEntity.MyEntityId
        join oneMoreEntity in dbContext.OneMoreEntities on myEntity.Id equals oneMoreEntity.MyEntityId
            select new MyJoinedEntity {
                Id = myEntity.Id,
                Name = myEntity.Name,
                OtherProperty = myOtherEntity.OtherProperty,
                OneMoreProperty = oneMoreEntity.OneMoreProperty
            };

    if (condition1) {
        joinedEntities = JoinWithRelated(joinedEntities);
    }

}

public IEnumerable<MyJoinedEntity> JoinWithRelated(IEnumerable<MyJoinedEntity> joinedEntities) {
    return from joinedEntity in joinedEntities
    join relatedEntity in dbContext.RelatedEntities on joinedEntity.Id equals relatedEntity.MyEntityId
        select new MyJoinedEntity(joinedEntity) {
            Comments = relatedEntity.Comments
        };
}
Daniel Gabriel
  • 3,939
  • 2
  • 26
  • 37
  • 1
    Thank you @Daniel, But what if I have many "IF" statements. In each If statement I don't actually know how many of previous IFs have gone true, so I don't know which fields to bring in "select new" part. Would you elaborate more on this please? if(selectedFromDropdown#1) should join from table#1 if(selectedFromDropdown#2) should join table#2 as well . . . – Moji Apr 15 '14 at 05:46
  • Thanks @Daniel. It was much of help and did work! I was just curious if there was another way which didn't need a method for every entity to join. Like a real dynamic query. – Moji Apr 16 '14 at 04:58
  • Well, you can put all of the queries into a single method if you want, and it'll be a dynamic query. These joins return an `IQueryable` which is not materialized yet - the query hasn't run. And after you have added in all the joins and call a method like `.ToList()`, then the query runs and executes your whole expression tree. – Daniel Gabriel Apr 16 '14 at 05:17
  • Thanks @Daniel. Actually, when I tried using IQueryable in my methods, I couldn't use the class constructor with arguments (e.g. MyJoinedEntity(joinedEntity)) because it gave me error doing so. Instead, I'm using IEnumerable, as you did in your sample. Wouldn't that be bad in sense of database performance? – Moji Apr 18 '14 at 07:32
  • `MyJoinedEntity` is your class. You would need to make a copy constructor yourself for every entity like that. An `IQueryable` is returned from a join of database entities automatically, you don't directly use it. Look at this link to understand the difference better: http://stackoverflow.com/questions/2876616/returning-ienumerablet-vs-iqueryablet – Daniel Gabriel Apr 18 '14 at 15:12
  • But this is not 'dynamic'. It is still a static combination of some entities which was not the goal of the question. – sahl04 Dec 11 '18 at 09:34