Optimizing Linq Query

Question

I am attaching a simple LINQ Query. I want to figure out if there is any better and efficient way of pulling these three fields (Field1 , Field2, Field3) as mentioned in LINQ query below.

from currentCons in ActiveConnections
join accounts in Accounts on currentCons.New_AccountId equals accounts.AccountId                                     
let Field1 =(from holder in MapHolders
        join entity in Entities on stringmap.ObjectTypeCode equals   entity.ObjectTypeCode
        where entity.Name == "New_Connection"
        where holder.AttributeName == "New_Data"
        where holder.AttributeValue ==  currentCons.New_DATA_Val
        select holder.Value)
 let Field2=(from holder in MapHolders
        join entity in Entities on stringmap.ObjectTypeCode equals entity.ObjectTypeCode
        where entity.Name == "New_Connection"
        where holder.AttributeName == "New_Type"
        where holder.AttributeValue == currentCons.New_Type
        select holder.Value)
let Field3=(from holder in MapHolders
        join entity in Entities on stringmap.ObjectTypeCode equals entity.ObjectTypeCode
        where entity.Name == "New_Connection"
        where holder.AttributeName == "New_EntryPoint"
        where holder.AttributeValue == currentCons.New_EntryPoint
        select holder.Value)
   where currentCons.ModifiedOn >= Convert.ToDateTime("1/1/2011")
   where currentCons.ModifiedOn <= Convert.ToDateTime("5/5/2012")
   select new 
   {
      conId = currentCons.ConnectionId,
      ConName = currentCons.name,
      TypeId = currentCons.Type,
      AccountId = currentCons.AccountId,
      OId = currentCons.OwnerId,
      BandwidthId = currentCons.Bandwidth,
      EntryPointId = currentCons.EntryPoint,
      Cap = Field1 ,
      Dir=Field2,
      EP=Field3,
      ModifiedOn = currentCons.ModifiedOn
    }

I know that the other way is to put directly in the Select Block . Whats the efficient way to do this ?

Any pointers are appreciated.

Thanks.

Answer 1

var fromDate =  new DateTime(2011,1,1);
var toDatePlusOneDay = new DateTime(2012,5,5).AddDays(1); // add one day to ensure 

var result = from currentCons in ActiveConnections
            join accounts in Accounts on currentCons.New_AccountId equals accounts.AccountId                                     
            where currentCons.ModifiedOn >= fromDate
            where currentCons.ModifiedOn < toDatePlusOneDay 
            from holder in MapHolders
            where (holder.AttributeName == "New_Data" && holder.AttributeValue == currentCons.New_DATA_Val) || (holder.AttributeName == "New_Type" && holder.AttributeValue == currentCons.New_Type) || (holder.AttributeName == "New_EntryPoint" && holder.AttributeValue == currentCons.New_EntryPoint)
            select new 
            {
              conId = currentCons.ConnectionId,
              ConName = currentCons.name,
              TypeId = currentCons.Type,
              AccountId = currentCons.AccountId,
              OId = currentCons.OwnerId,
              BandwidthId = currentCons.Bandwidth,
              EntryPointId = currentCons.EntryPoint,
              ModifiedOn = currentCons.ModifiedOn,
              Cap = (holder.AttributeValue == currentCons.New_DATA_Val) ? holder.Value : null,
              Dir = (holder.AttributeValue == currentCons.New_Type) ? holder.Value : null,
              EP = (holder.AttributeValue == currentCons.New_EntryPoint) ? holder.Value : null              
            }   

I removed the following since it's not used in any result

join entity in Entities on stringmap.ObjectTypeCode equals entity.ObjectTypeCode
        where entity.Name == "New_Connection"

Also please check if Cap/Dir/EP is nullable