none
Can we use IQueryable results for projection RRS feed

  • General discussion

  • Folks

    We have an entity (Company) that has 30+ related entities. We use projection to query on these relationships.

    For example: we have these entity relationships Company->CompanyDetail->Country  AND Company->CompanyContact->CompanyContactDetail

    So when we need to get Company details and the contact details we use projection like this:

    mycontext.Companies.Where(c => c.CompanyID == 123).Select(c => new
    				{
    					Company = c,
    					CompanyDetails = c.CompanyDetails.Where(d => d.LanguageID == 61).Select(cd => new
    					{
    						CompanyDetail = cd,
    						Country = cd.Country
    					}).FirstOrDefault(),
    					CompanyContacts = c.CompanyContacts.Select(cc => new
    					{
    						CompanyContact = cc,
    						CompanyContactDetail = cc.CompanyContactDetails.Where(d => d.LanguageID == 61)
    					}).AsEnumerable().Select(c1 => c1.Company);

    But as we have 30+ relationships that all rely upon the relationship Company->CompanyDetail->Country, we would like to break down the queries so that they could be more reusable(less chance of error)

    We envisage this type of scenario:

    Company GetCompanyContacts( int CompanyID, int LanguageID )
    {
    	Company company;
    	using (Context  mycontext = new Context)
    	{
    	IQueryable<Company> companyDetails = GetCompany(CompanyID, LanguageID );
    	IQueryable<Company> companyContact = GetCompanyContact(CompanyID, LanguageID );
    	// some how combine into a protection of company and ContactDetails
    	company = companyDetails...??
    	}
    	return company;
    }
    
    
    
    IQueryable<Company> GetCompany( int CompanyID, int LanguageID )
    {
    	return mycontext.Companies.Where(c => c.CompanyID == CompanyID).Select(c => new
    							{
    								Company = c,
    								CompanyDetails = c.CompanyDetails.Where(d => d.LanguageID == LanguageID).Select(cd => new
    								{
    									CompanyDetail = cd,
    									Country = cd.Country									
    								}).FirstOrDefault()
    						).AsEnumerable().Select(s => s.Company);
    }
    
    IQueryable<Company> GetCompanyContact( int CompanyID, int LanguageID )
    {
    	return mycontext.CompanyContacts.Where(c => c.CompanyID == CompanyID).Select(cc => new
    					{
    						CompanyContact = cc,
    						CompanyContactDetail = cc.CompanyContactDetails.Where(d => d.LanguageID == LanguageID)
    					}).AsEnumerable().Select(c1 => c1.Company);
    
    }

    This way we would have 30+ IQueryable methods that could be combined as needed

    Our question is How do we join them so that they can preform a projection query?

    Kindest Regards

    GregJF

    Sunday, September 2, 2012 11:21 PM

All replies

  • In this scenario, we often use Entity SQL instead of Linq to Entities. Entity SQL is more flexible, we can concatenating the string to build the query statement.
    Tuesday, September 4, 2012 1:37 AM