In join how to segregate the select statement

Question

In my project one table is connected with two or more tables ,To require wanted out put need to join them,Join is not my problem ,after join want to select desired column but from a segregate expression , as like bellow syntax:

public IEnumerable GetParent(string organogramType = "")
{
    var query = (from p in this.Context.CmnCompanies
    where organogramType != "" && !p.OrganogramType.Contains(organogramType) && p.OrganogramType != null
    join r in this.Context.CmnCompanyCategories on p.CompanyCategoryID equals r.CompanyCategoryID
    join s in this.Context.CmnCompanyReferences on p.RefID equals s.RefID
    join t in this.Context.CmnPartnerDetails on p.PartnerID equals t.PartnerID).Select(SelectSearchColumns).ToList();
    return query;   
}
public Expression<Func<CmnCompany, CmnCompanyReference, CmnPartnerDetail, dynamic>> SelectSearchColumns = (p, r,t) => new

{
    CompanyID = p.CompanyID,
    CompanyName=p.CompanyName,
    PartnerName=t.PartnerName,
    OrganogramType=p.OrganogramType,
    ParentID=p.ParentID,
    InceptionDate=p.InceptionDate,
    RefName=r.RefName,
};

Want to segregate select statement from my bellow linq syntax
base on method parameter=organogramType select column name will be change suppose organogramType (Company, office, departmentName) office then select columnn “CompanyName “ will be chage with Office,how to chage column name on run time

Show me ERROR MESSAGE: A query body must end with a select clause or a group clause

If have any query please ,thanks in advanced

Sergey Berezovskiy · Accepted Answer · 2013-02-17T08:45:29.853

1

You can't do that, because your expression needs three arguments, but from query you always receive single argument (which is sequence type). Also your query needs select statement after last join to eliminate error you see now. So, technically you can create type, which holds all three objects:

public class Foo
{
    public CmnCompany Company { get; set; }
    public CmnCompanyReference CompanyReference { get; set; }
    public CmnPartnerDetail PartnerDetail { get; set; }
}

And select that object from query (don't worry, query is not executed yet):

var query = 
   from p in this.Context.CmnCompanies
   where organogramType != "" && 
         !p.OrganogramType.Contains(organogramType) && 
         p.OrganogramType != null
   join r in this.Context.CmnCompanyCategories 
         on p.CompanyCategoryID equals r.CompanyCategoryID
   join s in this.Context.CmnCompanyReferences on p.RefID equals s.RefID
   join t in this.Context.CmnPartnerDetails on p.PartnerID equals t.PartnerID)
   select new Foo { 
       Company = p, 
       CompanyReference = s, 
       PartnerDetails = t 
   };

Then modify your expression (if property name matches original property name, you can skip property name specifying):

public Expression<Func<Foo, dynamic>> SelectSearchColumns = (f) => new    
{
    f.Company.CompanyID,
    f.Company.CompanyName,
    f.PartnerDetail.PartnerName,
    f.Company.OrganogramType,
    f.Company.ParentID,
    f.Company.InceptionDate,
    f.CompanyReference.RefName,
};

And do projection (query executed only when converted to list):

return query.Select(SelectSearchColumns).ToList();

If you will run SQL Profiler, you will see, that only columns selected in your expression are returned from database.

edited Feb 17 '13 at 08:45

answered Feb 17 '13 at 08:25

Sergey Berezovskiy

232,247
41
429
459

lazyberezovsky,Thanks for reply ,there is one problem remaining is it possible to use if in select statement. – shamim Feb 17 '13 at 08:41
if (organogramType !=""){CompanyName= p.CompanyName,}else{ DepartmentName= p.CompanyName,} – shamim Feb 17 '13 at 08:43
@shamim if you want to conditionally select different properties in your anonymous type, then nope, that is not possible. All you can do is assign different values to property via ternary operator `?:`. Like this `Name = p.IsDepartment ? p.DepartmentName : p.CompanyName` – Sergey Berezovskiy Feb 17 '13 at 08:43
Ilazyberezovsky,t’s not different properties ,same property just change the column name,please check my comment if condition syntax. – shamim Feb 17 '13 at 08:49
@shamim property with different name is a different property. Ah, I've got an idea - you can conditionally call different projection expressions on your query. `if (organogramType !="") return query.Select(SelectOrganogram).ToList(); else return query.Select(SelectSearchColumns).ToList()` BTW how you are going to define which properties your dynamic object have? – Sergey Berezovskiy Feb 17 '13 at 08:55
lazyberezovsky,is it possible to delete property from an anonymus type list,suppose my list properties are A,B,C after fill the anonymus list can I delete property B or C or any one – shamim Feb 17 '13 at 09:12
@shamim nope, you can't - anonymous types are static types, like any other class (Foo class above). The only difference is that anonymous type do not have name (actually they do have name, but name is generated by compiler). – Sergey Berezovskiy Feb 17 '13 at 09:19

In join how to segregate the select statement

1 Answers1