Select multiple columns in a LINQ query

LINQ is useful for querying as we can query different various data sources such as XML documents,SQL databases, CSV Files, JSON documents etc. , using C# or any other .NET language.

LINQ provides two different ways to write queries:

• Declarative query syntax which is SQL like syntax.It’s important to understand that the declarative syntax is also converted to extension method syntax at compile time.
• Query operators  which are defined as extension methods in the System.Linq namespace. Examples of these are Where,Select and OrderBy.

We can select values from a data source such as a c# list using Select operation in LINQ.Select operation also allows us to select multiple columns in a Linq query.

Selecting List items using  LINQ where clause

Suppose we have a List of items and we want to search for some values in the list. We can easily do that using the LINQ select operator.
For example, if we have a list of Employee classes declared as:

 var employeesLst = new List<Employee> { new Employee { Name = "emp1", Id = "1",Dept="Accounts" },
 new Employee { Name = "emp2", Id = "2",Dept="Finance" },
 new Employee { Name = "emp3", Id = "3",Dept="Finance" },
};

then we can filter the employees based on the Id, using the declarative syntax as:

 var selectedLst=from item in employeesLst
 where item.Id=2
 select item.Name;

It is important to remember that all the Language Integrated Query functionality is defined in the System.Linq namespace. So you need to add the following using statement when using the above code snippet.

using System.Linq;

LINQ data source is any object that implements generic IEnumerable<T> interface or non-generic IEnumerable interface.Common examples of such types are Array and ArrayList.Here we are using employeesLst ArrayList as the data source.

The equivalent method chaining syntax , using LINQ extension methods is:

employeesLst.Where(item=>item.Id=2).Select(item=>item.Name);

But if now we want to select multiple Dept and Name fields then we can not use this since this will be invalid syntax in LINQ:

var selectedLst=from item in employeesLst
select item.Id,item.Name;

Remember Select is a projection operation.Projection operations are used to transform an object into a new object containing properties which are required.LINQ is all about data and mainly two things:

1. Selecting the data: We use LINQ queries as above for selecting the data.
2. Transforming the data: We can transform the selected data to any desired format.For example converting XML data to JSON.

Select multiple columns in a LINQ query

Since in this scenario we require properties which are not contained by any existing object, we can use the anonymous type using the following syntax:

 var selectedLst=from item in employeesLst
 where item.Id=2
 select new { item.Id, item.Name };

Anonymous type is used to define a type that contains just read-only properties, using object initializer syntax. It is a convenient way to define a type you don’t need to reuse in multiple places. Since we don’t need to use the properties defined by the anonymous type outside our LINQ query, so we use the anonymous type here. But anonymous type can not contain any other type of members such as methods or constructors.

Select multiple columns in a LINQ query using extension methods and lambdas

We can write the above query using extension methods and lambdas as:

 employeesLst.Where(x.Id > 5)
 .Select(x=>new {
  item.Id, item.Name
  });

To order the values by a specific column you can use the OrderBy method and pass the column you want to order upon as a lambda:

employeesLst.Where(x.Id >5)
.Select(x=>new {
  item.Id, item.Name
  }).OrderBy(x => x.Id);

Convert selected columns in the LINQ query to a List:

If you want to convert the returned values to a list you can use the ToList method:

mployeesLst.Where(x.Id >5)
.Select(x=>new {
item.Id, item.Name
}).OrderBy(x => x.Id).ToList();

For Querying DataTable using LINQ syntax refer: Query DataTable using LINQ in C#

For merging multiple lists refer: Merge multiple Lists using LINQ Zip operator in C#

For LINQ Group By clause in C# refer: LINQ Group By clause in C#

For querying DataTable using LINQ refer:Query DataTable using LINQ in C#

.NET :What is New in .NET 9

 

Follow on: