Linq Query in C#

Praveen Kumar

6y1.3k.NET

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'

17 Replies

Know the answer? Post it — somebody with the same question will find it here.

Praveen Kumar6y ago
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
0
Praveen Kumar6y ago
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.Item1, Name = string.IsNullOrEmpty(c.Item2) ? c.Item2 : $"{c.Item2} | {c.Item2}" }), "Id", "Name");
}
}

-------------------------------------------------------------------------------------------------------------
0
Praveen Kumar6y ago
Hi Jignesh,

It's running and View getting error

0
Jignesh Kumar6y ago
Hi Praveen,

Below code looks correct it should work perfectly.
public Listint,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();
Listint, string, string>> tuples = wf.Select(x => new Tuple<int, string, string>(x.WorkflowGroupId, x.WorkflowGroupName, x.CompanyNumber)).ToList();
return tuples;
}
}
0
Praveen Kumar6y ago
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;
}
}

0
Jignesh Kumar6y ago
Hi Praveen,

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

0
Praveen Kumar6y ago
HI Jignesh,

Still i am working on it.
0
Jignesh Kumar6y ago
Hey Praveen is it resolved ?
0
Jignesh Kumar6y ago
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();
0
Praveen Kumar6y ago
Hi Jignesh,

0
Jignesh Kumar6y ago
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();
0
Praveen Kumar6y ago
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;
0
Jignesh Kumar6y ago
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();
}
0
Praveen Kumar6y ago
Hi Jignesh,

Linq query is fine, i need like linq entity to make it work.
0
Jignesh Kumar6y ago
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+
0
Salman Beg6y ago
Hey you can convert by using these links, http://www.sqltolinq.com/ http://www.linqpad.net/
0