Linq Query in C#

Question

Can anyone help me how to write linq code in C# since i am new to linq concept select Distinct WorkflowGroupId,WorkflowGroupName from ViewBookLevelDataGroupOrganizations DGO INNER JOIN ViewWorkflowGroupChildrenData WGC ON DGO.BookOrganizationNumber = WGC.BookNumber OR DGO.CompanyOrganizationNumber = WGC.CompanyNumber Where DGO.UserId='121604'

Jignesh Kumar · Answer

Hi Praveen, Below code looks correct it should work perfectly. public List int , string , string >> GetworkflowByUser(User user, int customerId) { if (!CheckDataEntitlements(user)) { var wrkflw = FindBy (o => o.CustomerId == customerId && o.TypeCode == "wg" ).OrderBy(o => o.Name).ToList(); return wrkflw.Select(x => new Tuple int , string , string >(x.Id, x.Name, x.OrganizationNumber)).ToList(); } else { var wf = (from DGO in DbContext.ViewBookLevelDataGroupOrganizations from WGC in DbContext.ViewWorkflowGroupChildrenDatas where DGO.UserId == user.UserId && (DGO.BookOrganizationNumber == WGC.BookNumber || DGO.CompanyOrganizationNumber == WGC.CompanyNumber) select new { WGC.WorkflowGroupId, WGC.WorkflowGroupName, WGC.CompanyNumber }).AsEnumerable(); List int , string , string >> tuples = wf.Select(x => new Tuple int , string , string >(x.WorkflowGroupId, x.WorkflowGroupName, x.CompanyNumber)).ToList(); return tuples; } }

Praveen Kumar · Answer

Hi Jignesh, CREATE view ViewWorkflowGroupChildrenDatas AS select wfGroup.Id as WorkflowGroupId , wfGroup.Name as WorkflowGroupName , bookRel.ParentOrgName as BookName , bookRel.ParentNumber as BookNumber , companyRel.ParentOrgName as CompanyName , companyRel.ParentNumber as CompanyNumber from organizations wfgroup inner join viewrelatedOrganizations workflowRel on workflowRel.parentId = wfgroup.id inner join viewrelatedOrganizations clientRel on clientRel.ChildId =workflowRel.ChildId and clientRel.ParentTypeCode='CL' inner join viewRelatedOrganizations bookRel on bookRel.ChildId = clientRel.ParentId and bookRel.ParentTypeCode='BK' inner join viewRelatedOrganizations companyRel on companyRel.ChildId = bookRel.ParentId and companyRel.ParentTypeCode='CO' where wfgroup.typecode='WG' and workflowRel.ChildTypeCode='CG' UNION select wfGroup.Id as WorkflowGroupId , wfGroup.Name as WorkflowGroupName , bookRel.ParentOrgName as BookName , bookRel.ParentNumber as BookNumber , companyRel.ParentOrgName as CompanyName , companyRel.ParentNumber as CompanyNumber from organizations wfgroup inner join viewrelatedOrganizations workflowRel on workflowRel.parentId = wfgroup.id inner join viewrelatedOrganizations calcGroupRel on calcGroupRel.ChildId = workflowRel.ChildId and calcGroupRel.ParentTypeCode = 'CG' inner join viewrelatedOrganizations clientRel on clientRel.ChildId =calcGroupRel.ParentId and clientRel.ParentTypeCode='CL' inner join viewRelatedOrganizations bookRel on bookRel.ChildId = clientRel.ParentId and bookRel.ParentTypeCode='BK' inner join viewRelatedOrganizations companyRel on companyRel.ChildId = bookRel.ParentId and companyRel.ParentTypeCode='CO' where wfgroup.typecode='WG' and workflowRel.ChildTypeCode='BG' GO

Praveen Kumar · Answer

Hi jignesh, public class ViewWorkflowGroupChildrenData { [Key] public int Id { get; set; } public int WorkflowGroupId { get; set; } public string WorkflowGroupName { get; set; } public string BookName { get; set; } public string BookNumber { get; set; } public string CompanyName { get; set; } public string CompanyNumber { get; set; } } ------------------------------------------------------------------ public IDbSet ViewWorkflowGroupChildrenDatas { get; set; } ----------------------------------------------------------------------------------------------------- public static SelectList WorkflowGroupsByUser(int customerId, bool addBlankOption = false) { DbContextScopeFactory DbContext = new DbContextScopeFactory(new Redi2ContextFactory(new LoginSecurityRepo().GetRMApplConnection())); AmbientDbContextLocator ambientContextLocator = new AmbientDbContextLocator(); LoginSecurityRepo _Login = new LoginSecurityRepo(); User user = _Login.GetCurrentUser(); var repo = new WorkflowRepository(ambientContextLocator); using (var ctx = DbContext.CreateReadOnly()) { //var companies = repo.GetworkflowByUser(user, customerId); //if (addBlankOption) // companies.Insert(0, new Organization { Id = 0, Name = " " }); //return new SelectList(companies.Select(c => new { c.Id, Name = string.IsNullOrEmpty(c.OrganizationNumber) ? c.Name : $"{c.Name} | {c.OrganizationNumber}" }), "Id", "Name"); var companies = repo.GetworkflowByUser(user, customerId); if (addBlankOption) companies.Insert(0, new Tuple (0, " ")); return new SelectList(companies.Select(c => new { c.Item1, Name = string.IsNullOrEmpty(c.Item2) ? c.Item2 : $"{c.Item2} | {c.Item2}" }), "Id", "Name"); } } -------------------------------------------------------------------------------------------------------------

Praveen Kumar · Answer

Hi Jignesh, It's running and View getting error

Praveen Kumar · Answer

Hi Jignesh public List > GetworkflowByUser(User user, int customerId) // public List GetworkflowByUser(User user, int customerId) { if (!CheckDataEntitlements(user)) { var wrkflw = FindBy (o => o.CustomerId == customerId && o.TypeCode == "wg").OrderBy(o => o.Name).ToList(); return wrkflw.Select(x => new Tuple (x.Id, x.Name, x.OrganizationNumber)).ToList(); } else { var wf = (from DGO in DbContext.ViewBookLevelDataGroupOrganizations from WGC in DbContext.ViewWorkflowGroupChildrenDatas where DGO.UserId == user.UserId && (DGO.BookOrganizationNumber == WGC.BookNumber || DGO.CompanyOrganizationNumber == WGC.CompanyNumber) select new { WGC.WorkflowGroupId, WGC.WorkflowGroupName, WGC.CompanyNumber }).ToList(); List > tuples = wf.Select(x => new Tuple (x.WorkflowGroupId, x.WorkflowGroupName, x.CompanyNumber)).ToList(); return tuples; } }

Jignesh Kumar · Answer

Hi Praveen, Your model organization had different field than this select statement. That is why both are not matching.

Praveen Kumar · Answer

Hi jignesh

Praveen Kumar · Answer

HI Jignesh, Still i am working on it.

Jignesh Kumar · Answer

Hey Praveen is it resolved ?

Jignesh Kumar · Answer

Hi Praveen, Change or condition in where clause var result = (from DGO in DbContext.ViewBookLevelDataGroupOrganizations join WGC in DbContext.ViewWorkflowGroupChildrenData on DGO.BookOrganizationNumber = WGC.BookNumber where DGO.UserId=user.UserId || DGO.CompanyOrganizationNumber = WGC.CompanyNumber select new { WorkflowGroupId, WorkflowGroupName }).Distinct();

Praveen Kumar · Answer

Hi Jignesh,

Jignesh Kumar · Answer

Hi Praveen, Please find beloq wuery which will help you, var result = (from DGO in DbContext.ViewBookLevelDataGroupOrganizations join WGC in DbContext.ViewWorkflowGroupChildrenData on DGO.BookOrganizationNumber = WGC.BookNumber || DGO.CompanyOrganizationNumber = WGC.CompanyNumber where DGO.UserId=user.UserId select new { WorkflowGroupId, WorkflowGroupName }).Distinct();

Praveen Kumar · Answer

Hi Jignesh, var result = (from DGO in context.ViewBookLevelDataGroupOrganizations join WGC in context.ViewWorkflowGroupChildrenData on DGO.BookOrganizationNumber = WGC.BookNumber || DGO.CompanyOrganizationNumber = WGC.CompanyNumber where DGO.UserId= '121604' select new { WorkflowGroupId, WorkflowGroupName }).Distinct(); In my Project they following this entity format.Could you send me like this and below i tried and incomplete var wfu = DbContext.ViewBookLevelDataGroupOrganizations .Join(DbContext.ViewWorkflowGroupChildrenDatas, dgo => dgo.BookOrganizationNumber, wgc => vcl.BookNumber, (dgo, wgc) => new { dgo, wgc } || dgo => dgo.dgo.CompanyOrganizationNumber, wgc => vcl.CompanyNumber, (dgo, wgc) => new { dgo, wgc }) .Where(m => m.dgo.dgo.UserId == user.UserId) .Select(m => m.wgc).ToList(); return wfu;

Jignesh Kumar · Answer

Replace table name with your entity. It should work same way Ref : https://www.c-sharpcorner.com/UploadFile/ff2f08/sql-join-in-linq-linq-to-entity-linq-to-sql/ using (var context = new YourDBContext()) { var result = (from DGO in context.ViewBookLevelDataGroupOrganizations join WGC in context.ViewWorkflowGroupChildrenData on DGO.BookOrganizationNumber = WGC.BookNumber || DGO.CompanyOrganizationNumber = WGC.CompanyNumber where DGO.UserId= '121604' select new { WorkflowGroupId, WorkflowGroupName }).Distinct(); }

Praveen Kumar · Answer

Hi Jignesh, Linq query is fine, i need like linq entity to make it work.

Jignesh Kumar · Answer

Hi, You can do like this, var result = (from DGO in ViewBookLevelDataGroupOrganizations join WGC in ViewWorkflowGroupChildrenData on DGO.BookOrganizationNumber = WGC.BookNumber || DGO.CompanyOrganizationNumber = WGC.CompanyNumber where DGO.UserId= '121604' select new { WorkflowGroupId, WorkflowGroupName }). Distinct() ; For more reference, https://forums.asp.net/t/1715694.aspx?Creating+LINQ+queries+with+distinct+and+multiple+inner+joins+

Salman Beg · Answer

Hey you can convert by using these links, http://www.sqltolinq.com/ http://www.linqpad.net/