I am quite new with linq and am having a few difficulties get a repository method to work.
I have 2 tables EMPOS and PSLDW which contain position and relationship data respectively.
I am trying to make a nodedata model to bind to an orgchart object.
So far I have made repository methods to access data in the HR system tabel (EMPOS and PSLDW), this is an oracle database.
I have also made a model called "OrgChartDataItem" which has the correct structure required in order to bind to the Telerik OrgChart Object.
public class OrgChartDataItem
{
[Key]
public string NodeID { get; set; }
public string Title { get; set; }
public string ParentID { get; set; }
}
The position table (EMPOS) holds the NodeID (JobCode) which is called "POSNUMBERA" and Title (JobTitle) which is called "POSTITLEA".
The relationship table (PSLDW) holds the ParentID (Managers JobCode) which is called "RELRELAT01".
I have repository methods to return a distinct list of current posistion called CurrentPOS().
I then use the unitofwork.PsldwRepository.Get() method to get the contents of the relationship table.
Below is my attempt to use these methods to create a collection with a type of "OrgChartDataItem", you can see I join CurrentPOS() and the PsldwRepository.Get() using EMPOS field POSNUMBERA and PSLDW field RELCODEA (JobCode).
public IEnumerable<OrgChartDataItem> GetNodeData(string Mid)
{
UnitOfWork uow = new UnitOfWork();
var list = (from p in CurrentPOS()
join r in uow.PsldwRepository.Get()
on p.POSNUMBERA equals r.PDTCODEA
where r.RELRELAT01A == Mid
select p).Distinct().ToList();
OrgChartDataItem ocdi = (from l in list
join m in uow.PsldwRepository.Get()
on l.POSNUMBERA equals m.PDTCODEA
where m.RELRELAT01A == Mid
select new {
ocdi.NodeID = l.POSNUMBERA,
ocdi.Title = l.POSTITLEA,
ocdi.ParentID = m.RELRELAT01A
}).ToList();
return (ocdi);
}
I am getting the following errors and am not sure how I should structure my query to provide the described result, can anyone help?
Andy
Error 4 An anonymous type cannot have multiple properties with the same name C:\Users\AMcInnes\documents\visual studio 2010\Projects\OrgChart3\OrgChart3\DAL\BusinessLayer.cs 56 42 OrgChart3
Error 5 An anonymous type cannot have multiple properties with the same name C:\Users\AMcInnes\documents\visual studio 2010\Projects\OrgChart3\OrgChart3\DAL\BusinessLayer.cs 57 42 OrgChart3
Error 6 Cannot implicitly convert type 'OrgChart3.Models.OrgChartDataItem' to 'System.Collections.Generic.IEnumerable<OrgChart3.Models.OrgChartDataItem>'. An explicit conversion exists (are you missing a cast?) C:\Users\AMcInnes\documents\visual studio 2010\Projects\OrgChart3\OrgChart3\DAL\BusinessLayer.cs 59 21 OrgChart3
Error 1 Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access. C:\Users\AMcInnes\documents\visual studio 2010\Projects\OrgChart3\OrgChart3\DAL\BusinessLayer.cs 55 42 OrgChart3
Error 2 Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access. C:\Users\AMcInnes\documents\visual studio 2010\Projects\OrgChart3\OrgChart3\DAL\BusinessLayer.cs 56 42 OrgChart3
Error 3 Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access. C:\Users\AMcInnes\documents\visual studio 2010\Projects\OrgChart3\OrgChart3\DAL\BusinessLayer.cs 57 42 OrgChart3
You can specify the type you are selecting to, notice "select new OrgChartDataItem" below:
OrgChartDataItem ocdi = (from l in list
join m in uow.PsldwRepository.Get()
on l.POSNUMBERA equals m.PDTCODEA
where m.RELRELAT01A == Mid
select new OrgChartDataItem {
ocdi.NodeID = l.POSNUMBERA,
ocdi.Title = l.POSTITLEA,
ocdi.ParentID = m.RELRELAT01A
}).ToList();
Darrell Norton, MVP
Darrell Norton's Blog Please click "Mark as Answer" if this helped you.
public IEnumerable<OrgChartDataItem> GetNodeData(string Mid)
{
string Jid = GetJobById(Mid);
UnitOfWork uow = new UnitOfWork();
IEnumerable<OrgChartDataItem> ocdi = (from r in uow.PsldwRepository.Get()
join p in CurrentPOS() on r.PDTCODEA equals p.POSNUMBERA
where r.RELRELAT01A == Jid
select new OrgChartDataItem(p.POSNUMBERA, p.POSTITLEA, r.RELRELAT01A));
return (ocdi);
}
Which seems to work when binding to a RadGrid object, so thank you for helping me confirm I was doing it in the right way. Will my updated more concise version return a distinct list of node IDs?
The reason I ask is because on the EMPOS table which I am querying, Employee ID is the primary key field and JobCode which is my primary key field for the orgchart, or NodeID, is a foreign key and can exist multiple times in EMPOS.
For my orgchart I am going to populate each node with a list of employees who have that job code, this way I get a narrower tree.
The problem I am having now is defining this as the data source for the RadOrgChart object, do you have any experience with Telerik Rad Controls?
Thanks again,
Andy
Marked as answer by mcinnes01 on Jan 31, 2013 04:21 PM
mcinnes01
Member
16 Points
120 Posts
Help using linq to select multiple fields in to an object
Nov 05, 2012 02:33 PM|LINK
Hi,
I am quite new with linq and am having a few difficulties get a repository method to work.
I have 2 tables EMPOS and PSLDW which contain position and relationship data respectively.
I am trying to make a nodedata model to bind to an orgchart object.
So far I have made repository methods to access data in the HR system tabel (EMPOS and PSLDW), this is an oracle database.
I have also made a model called "OrgChartDataItem" which has the correct structure required in order to bind to the Telerik OrgChart Object.
public class OrgChartDataItem { [Key] public string NodeID { get; set; } public string Title { get; set; } public string ParentID { get; set; } }The position table (EMPOS) holds the NodeID (JobCode) which is called "POSNUMBERA" and Title (JobTitle) which is called "POSTITLEA".
The relationship table (PSLDW) holds the ParentID (Managers JobCode) which is called "RELRELAT01".
I have repository methods to return a distinct list of current posistion called CurrentPOS().
I then use the unitofwork.PsldwRepository.Get() method to get the contents of the relationship table.
Below is my attempt to use these methods to create a collection with a type of "OrgChartDataItem", you can see I join CurrentPOS() and the PsldwRepository.Get() using EMPOS field POSNUMBERA and PSLDW field RELCODEA (JobCode).
public IEnumerable<OrgChartDataItem> GetNodeData(string Mid) { UnitOfWork uow = new UnitOfWork(); var list = (from p in CurrentPOS() join r in uow.PsldwRepository.Get() on p.POSNUMBERA equals r.PDTCODEA where r.RELRELAT01A == Mid select p).Distinct().ToList(); OrgChartDataItem ocdi = (from l in list join m in uow.PsldwRepository.Get() on l.POSNUMBERA equals m.PDTCODEA where m.RELRELAT01A == Mid select new { ocdi.NodeID = l.POSNUMBERA, ocdi.Title = l.POSTITLEA, ocdi.ParentID = m.RELRELAT01A }).ToList(); return (ocdi); }I am getting the following errors and am not sure how I should structure my query to provide the described result, can anyone help?
Andy
DarrellNorto...
All-Star
86555 Points
9624 Posts
Moderator
MVP
Re: Help using linq to select multiple fields in to an object
Nov 05, 2012 03:11 PM|LINK
You can specify the type you are selecting to, notice "select new OrgChartDataItem" below:
OrgChartDataItem ocdi = (from l in list join m in uow.PsldwRepository.Get() on l.POSNUMBERA equals m.PDTCODEA where m.RELRELAT01A == Mid select new OrgChartDataItem { ocdi.NodeID = l.POSNUMBERA, ocdi.Title = l.POSTITLEA, ocdi.ParentID = m.RELRELAT01A }).ToList();Darrell Norton's Blog
Please click "Mark as Answer" if this helped you.
mcinnes01
Member
16 Points
120 Posts
Re: Help using linq to select multiple fields in to an object
Nov 05, 2012 03:52 PM|LINK
Hi Darrell,
Thanks I got to this eventually:
public IEnumerable<OrgChartDataItem> GetNodeData(string Mid) { string Jid = GetJobById(Mid); UnitOfWork uow = new UnitOfWork(); IEnumerable<OrgChartDataItem> ocdi = (from r in uow.PsldwRepository.Get() join p in CurrentPOS() on r.PDTCODEA equals p.POSNUMBERA where r.RELRELAT01A == Jid select new OrgChartDataItem(p.POSNUMBERA, p.POSTITLEA, r.RELRELAT01A)); return (ocdi); }Which seems to work when binding to a RadGrid object, so thank you for helping me confirm I was doing it in the right way. Will my updated more concise version return a distinct list of node IDs?
The reason I ask is because on the EMPOS table which I am querying, Employee ID is the primary key field and JobCode which is my primary key field for the orgchart, or NodeID, is a foreign key and can exist multiple times in EMPOS.
For my orgchart I am going to populate each node with a list of employees who have that job code, this way I get a narrower tree.
The problem I am having now is defining this as the data source for the RadOrgChart object, do you have any experience with Telerik Rad Controls?
Thanks again,
Andy
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Help using linq to select multiple fields in to an object
Nov 06, 2012 03:46 AM|LINK
Hello,
Considering it that this is a 3-rd party control, I suggest you trying to post your issue at：
http://www.telerik.com/community/forums.aspx