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#
- Add List Element Based on Conditional with Linq Expression
- Linq Expression Tree OrderByDescending with custom comparer
- Dynamically Building Linq to Entities Lambda Expression with Concatenated Properties
- Don't understand compile error for method call with lambda expression
- Linq expression in C# with no argument
- Error-"The specified LINQ expression contains references to queries that are associated with different contexts."
- How to pass a func<> in linq to sql with a join
- LINQ Query Syntax for Self Join
- How to make a condition with EF Core 5 using concrete type (aka "The LINQ expression could not be translated" issue)?
- Regular Expression to divide a string with pipe-delimited into multiple groups
- C# populate array with unique ints No Linq or ArrayLists;
- Linq does not work with MonoDevelop/Mono last version on Mac OSX 10.6.4
- Regular Expression with logical AND NOT functionality
- C# expression body with {get;} vs without
- How to work with Expression Trees not working using WCF since they are not serializable?
- What is this lambda expression with Unity Resolver doing
- FirstOrDefault in a Join with QueryOver
- How to build an anonymous object with expression trees
- Table Join performance issue with Entity Framework
- Specify lambda expression with Generics but having a templated type
- Parsing XML with key value pair in C# using linq
- Hashing Password Database with LINQ to SQL
- How do I use multiple IDs from a table with an INNER JOIN using SQL?
- Problem with Inner join in Sql-server 2005
- How I can parse substring with regular expression ?
- How to test string with regular expression in C#?
- Using Generics with LINQ Inheritance
- Having trouble with regular expression
- The execution time of my threaded code does not benefit from threads with Join
- C# regular expression for finding forms with input tags in HTML?
- Can a custom GetHashcode implementation cause problems with Dictionary or Hashtable's "buckets"
- Running UI tests with Chrome Driver in GitLab CI/CD pipeline
- ExpandableObjectConverter properties are not showing in propertyGrid c#
- microsoft report landscape issue
- datagrid binding not working for one datagrid but is for another
- Unable to get object from top hits aggregation
- Why does PowerShell Pipeline.Output.ReadToEnd return an empty collection?
- How to display a signed three bytes hexadecimal output in C#?
- How can I drag a file to an exe I create (drag and drop over the Icon) and have it as an argument when the exe start running?
- Create JSON array with single key c#
- Do backslashes count twice when creating a directory, but only once when getting full path?
- PropertyGrid does not raise PropertyValueChanged event
- Retrieve 2 Access Tokens on Interactive Authentication in Azure AD
- how to check the app is installed for first time
- Can a Delphi DLL be PInvoked and used in C#?
- Adding a CosmosDB entity, (Unable to resolve iD for entity of type Tenant)
- ManualTrigger in Azure WebJob without outputting to queue at end
- How to show progress status for a long-time-consuming function?
- Connect local database mdf Visual Studio
- How can I draw a single pixel?