For that, I’m deiced to the self Join.I have table Employees with column Emp_Id and Mng_Id.Now I want to perform self join on this.I want to show the employee name and his manager’s name.
Sql Table
CREATE TABLE [dbo].[Employee](
[Emp_Id] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[Mng_Id] [int] NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[Emp_Id] ASC
)
) ON [PRIMARY]
Sql query:-
select e.Name EmployeeName,m.Name Managername from Employee e
Left join Employee m on e.Mng_Id=m.Emp_Id
Linq Query:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApplication
{
class Program
{
static void Main(string[] args)
{
DemoDataBaseEntities db = new DemoDataBaseEntities();
var result = from e in db.Employees
join m in db.Employees on e.Mng_Id equals m.Emp_Id into temp
from res in temp.DefaultIfEmpty()
select new
{
Emploayee = e.Name,
Manager = res.Name
};
}
}
}
Now i want to show “supermanager” if Mng_Id is null that means Employee doesn’t have any Mng_Id
Sql Query:-
select e.Name EmployeeName,Case when m.Name is NULL then 'SuperMager' else m.Name end as ManagerName from Employee e
Left join Employee m on e.Mng_Id=m.Emp_Id
Linq Query:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApplication
{
class Program
{
static void Main(string[] args)
{
DemoDataBaseEntities db = new DemoDataBaseEntities();
var result = from e in db.Employees
join m in db.Employees on e.Mng_Id equals m.Emp_Id into temp
from res in temp.DefaultIfEmpty()
select new
{
Emploayee = e.Name,
Manager = res.Name == null ? "SuperManager" : res.Name
};
}
}
}
The post Understand Self Join in Linq | Self Join with Lambada Expression appeared first on Software Development | Programming Tutorials.
Read More Articles
- Write a value which contain comma to a CSV file in c#?
- Reading CSV File with cells containing commas c#
- Split CSV with columns may contain ‘,’ Comma C#
- [Simple Way]-Cascading DropDownList in Asp.Net Mvc Using Jquery Ajax
- [Simple Way]-How to get data from database using JQuery Ajax in asp net MVC
- [Simple Way]-ASP.NET Core Upload Multiple File Web API model
- [Simple Way]- Image Upload in .NET Core Web API
- [Easy Way]-Receive File and other form data together in ASP.NET Core Web API
- Replace image in word document using C#
- How to add new rows to an existing word document table in C#
- LINQ to XML with join and GroupBy(). Is this correct?
- Linq expression to call Contains method with nullable parameter
- Linq to SQL Left Join with where clause looking for condition or null
- Trouble with linq query syntax group by expression
- Linq for self join
- Joining 6 tables together with inner joins & a left outer join - LINQ
- Left outer join with a table of only foreign keys using Linq in Entity Framework
- Serialize a class with Linq expression properties for SQL Server Session State
- LINQ to SQL - Self Join Child to Parent (Same Table)
- Having trouble with a Linq Join statement
- Translate t-sql query to LINQ (left join with subquery/nested right join)
- Linq Expression string with inline values
- Creating a Regular Expression with boundery
- Linq to Sql with Stored Procedure
- Parallelize LINQ Query with Let, Select Keywords
- Help with a regex expression
- Linq to SQL / C#: How to Order By using property in cross reference table with lambda expressions
- edit delimited string with regular expression
- Need help with regular expression to parse a string of e-mail addresses in C#
- Is this possible with LINQ-to-Entities query using Lambda Expression
- Problems with the following regular expression in RegularExpressionValidator control
- Dealing with LINQ / HttpContext.Application & WebServices
- Bad performance on deleting referenced database rows with Linq To SQL
- read rss items with linq
- How do I insert entities with Linq To SQL where I wish to specify their identities - eg, pre-populating tables on DB creation?
- How do you update an object with Linq 2 SQL without rowversion or timestamp?
- Web api getbyid with regular expression aspi.net web api 2
- Expression Tree calling an Expression with third parameter
- Linq query geting list by comparing Id with an list of objects that have ids
- LINQ to Entities does not recognize the method 'System.Object get_Item(System.String)' method cannot be translated into a store expression
- Backgroundworker is canceled and locked? How its possible?
- Return to the same view after asp-action
- How to register this class using autofac?
- Unable to cast object of type 'System.String' to type 'Microsoft.Office.Interop.Outlook.Store
- Visual C# best button adding solution
- WPF databinding to interface cannot find property
- Unity Camera animation not working?
- How would you determine if a generic type interface is implemented for a variables type (not a pre-known type)?
- Updating table in database using string from database?
- How to create html/aspx page on button click which holds database fields?
- How to access anonymous control's result?
- Using FileOpenPicker in Windows 8 Store Apps to browse your own resource directory on page startup
- Extracting data from XMLDocument
- Why cannot c# or c++ differentiate methods based on return type?
- AWS StepFunctions... Template error: instance of Fn::Sub references invalid resource attribute
- how do I reference my public class property, Event.Day, in my other class' method?
- System.Printing to return printqueue status - properties never return true
- Refresh Countdown Timer in .Net From Client Side?
- How to store name-value pairs in asp.net profile
- c# - represent array of integers in bit field