Linq Syntax - Selecting Multiple Columns

Linq Syntax - Selecting multiple columns

As the other answers have indicated, you need to use an anonymous type.

As far as syntax is concerned, I personally far prefer method chaining. The method chaining equivalent would be:-

var employee = _db.EMPLOYEEs
    .Where(x => x.EMAIL == givenInfo || x.USER_NAME == givenInfo)
    .Select(x => new { x.EMAIL, x.ID });

AFAIK, the declarative LINQ syntax is converted to a method call chain similar to this when it is compiled.

UPDATE

If you want the entire object, then you just have to omit the call to Select(), i.e.

var employee = _db.EMPLOYEEs
    .Where(x => x.EMAIL == givenInfo || x.USER_NAME == givenInfo);

Select Multiple Fields from List in Linq

Anonymous types allow you to select arbitrary fields into data structures that are strongly typed later on in your code:

var cats = listObject
    .Select(i => new { i.category_id, i.category_name })
    .Distinct()
    .OrderByDescending(i => i.category_name)
    .ToArray();

Since you (apparently) need to store it for later use, you could use the GroupBy operator:

Data[] cats = listObject
    .GroupBy(i => new { i.category_id, i.category_name })
    .OrderByDescending(g => g.Key.category_name)
    .Select(g => g.First())
    .ToArray();

Select multiple columns with linq to sql

You current request will query the whole user (select *).

In most cases, it is enough and you can access to each column separately :

var user = mycontext.Users.SingleorDefault(p => p.UserId == 1);

var userName = user.Username;
var password = user.Password;
// etc...

If you only need one (or several columns) but not the whole user, you can do it like this:

var userInfo = mycontext.Users.Where(p => p.UserId == 1).Select(p => new {p.UserName, p.Password}).SingleOrDefault();

var userName = userInfo.UserName;
var password = userInfo.Password;

If you prefer a collection of string in result, you can do it this way:

List<string> userInfo = mycontext.Users.Where(p => p.UserId == 1).Select(p => new List<string> { p.UserName, p.Password }).SingleOrDefault();

var username = userInfo[0];
var password = userInfo[1];

This way, the generated query will looks like Select UserName, Password From UserTable. Its usefull only if your table has a lots of columns or a heavy one (blob for example).

How it works: by calling the Where extension methods firstly, you tell .NET to not run the query immediately. It will be executed on the SingleOrDefault call. But at this moment, the query has been completed and the whole part is done in SQL.

Select multiple columns from Linq query

You wrote:

CUstomerBenefits - CustomerID, BenefitID (Both IDs have a relation to
their relevant tables)

I interpret this as meaning that there is a table CustomerBenefit that contains, well, benefits and that the CUstomerBenefits table in turn is a link between the customer and the benefits. And now you want to get a list of all the benefits that a specific customer has?

This should do the trick:

IEnumerable<CustomerBenefit> GetData =
    from c in MyContext.CUstomerBenefits
    where c.CustomerId == UserId && c.Active == true
    orderby c.CustomerBenefit.BenType descending
    select c.CustomerBenefit;

Selecting multiple columns for updating in Linq

Yes, there is a way to specify exactly which columns you want.

No, you can't use that method to update data.

When fetching data using entity framework DbSet<...>, there are two methods: fetch the complete row of the table, or only fetch certain properties of the row.

The first method is used, if you execute the query without using Select. If you do this, the data is copied to the DbContext.ChangeTracker.

Other methods like DbSet.Find and IQueryble.Include will also copy the fetched data to the ChangeTracker.

If you use Select to specify the data that you want to fetch, then the fetched data will not be copied into the ChangeTracker.

When you call DbContext.SaveChanges, the ChangeTracker is used to determine what items are changed or removed and thus need to be updated.

The ChangeTracker keeps the original fetched data, and a copy of it. You get the reference to the copy as the result of your changes. So whenever you change the values of properties of your reference to the copy, they are changed in the copy that is in the ChangeTracker.

When you call SaveChanges, the copy is compared to the original in the ChangeTracker, to detect which properties are changed.

To improve efficiency, if you don't plan to update the fetched data, it is wise to make sure that the fetched data is not in the ChangeTracker.

When using entity framework to fetch data, always use Select and fetch only the properties that you actually plan to use. Only query without Select if you plan to change the fetched data.

Change = update properties, or remove the complete row. Also: only use Find and Include if you plan to update the fetched data.

You want to update the fetched row

Hence you have to fetch the complete row: don't use Select, fetch the complete row.

If you want to fetch the item by primary key, consider to use DbSet.Find. This has the small optimization that if it is already in the ChangeTracker, then the data won't be fetched again.

Consider to write SQL for this

Usually you don't have to update thousands of items on a regular basis. However, if you have to do this often, consider to update using sql:

using (var dbContext = new MyDbContext(...))
{
    const string sqlText = @"Update products
        SET Price = @Price, IsAvailable = @IsAvailable....
        Where SellerId = @SellerId;";
    var parameters = new object[]
    {
        new SqlParameter("@SellerId", sellerId),
        new SqlParameter("@Price", newPrice),
        new SqlParameter("@IsAvailable", newAvailability),
    };
    dbContext.DataBase.ExecuteSqlCommand(sqlText, parameters);
}

(You'll have to check the validity of the SQL command in my example. Since I use entity framework, my SQL is a bit rusty.)

By the way: although this method is very efficient, you'll lose the advantages of entity framework: the decoupling of the actual database from the table structure: the names of your tables and columns seep through until this statement.

My advice would be only to use direct SQL for efficiency: if you have to update quite often. Your DbContext hides the internal layout of your database, so make this method part of your DbContext

public void UpdatePrice(int sellerId, bool IsAvailable, decimal newPrice)
{
    const string sqlText = ...
    var params = ...
    this.Database.ExecuteSqlCommand(sqlText, params);
}

Alas, you'll have to call this once per update, there is no SQL command that will update thousands of items with different prices in one SQLcommand
}

Selecting multiple columns with linq query and lambda expression

Not sure what you table structure is like but see below.

public NamePriceModel[] AllProducts()
{
    try
    {
        using (UserDataDataContext db = new UserDataDataContext())
        {
            return db.mrobProducts
                .Where(x => x.Status == 1)
                .Select(x => new NamePriceModel { 
                    Name = x.Name, 
                    Id = x.Id, 
                    Price = x.Price
                })
                .OrderBy(x => x.Id)
                .ToArray();
         }
     }
     catch
     {
         return null;
     }
 }

This would return an array of type anonymous with the members you require.

Update:

Create a new class.

public class NamePriceModel 
{
    public string Name {get; set;}
    public decimal? Price {get; set;}
    public int Id {get; set;}
}

I've modified the query above to return this as well and you should change your method from returning string[] to returning NamePriceModel[].

C# Linq to select multiple columns by group and apply sum(aggregate function) on value field at the same time

What you want in order to perform any kind of aggregation of data is to use is group by. In your current attempt you just projected a new object. See this:

var result = (from item in getvalues()
                group item.Value by new { item.Year, item.Month, item.Location } into g
                select new {
                    Year = g.Key.Year,
                    Month = g.Key.Month,
                    Location = g.Key.Location,
                    Total = g.Sum()
                });

And in method syntax:

var result = getvalues().GroupBy(item => new { item.Year, item.Month, item.Location }, 
                                 selector => selector.Value)
                        .Select(grouping => new
                        {
                            Year = grouping.Key.Year,
                            Month = grouping.Key.Month,
                            Location = grouping.Key.Location,
                            Total = grouping.Sum()
                        });

Also - It is a shame to do ToList on getvalues - it executes the previous query and retrieves the data causing all further querying to take place in memory instead as sql in database.

How to Select Multiple Fields from DataGridView using LINQ in C# WinForms

Think I can translate this one:

//all pages hits and the IPs hitting them report
select page, ip, count(page)
from [LogFileName]
group by page, ip
order by count(page) desc

as

var pageCountQuery = (dataGridViewIISDateTime.Rows.Cast<DataGridViewRow>()
    .Where(r => r.Cells[9].Value != null && r.Cells[5].Value != null)
    .Select(r => new { Page = r.Cells[9].Value, IP = r.Cells[5].Value })
    .GroupBy(pageip => pageip)
    .OrderByDescending(g => g.Count())
    .Select(g => new { PAGE = g.Key.Page, IP = g.Key.IP, HITS = g.Count() })).ToList();

You haven't said what column HTTP code is in.. But the second SQL you've posted has syntax errors and would only really work in MySQL, and even then only if ONLY_FULL_GROUP_BY is deactivated

LINQ search by multiple columns

You could do it using either one of these queries.

students = students.Where(x => (Fname == null || x.FirstName==Fname)
        && (Lname == null || x.LastName==Lname)
        && (ClassDate == null || x.classdate==ClassDate));

or

students = students.Where(x => x.FirstName==(Fname ?? x.FirstName)
        && x.LastName==(Lname ?? x.LastName)
        && x.classdate==(ClassDate??x.classdate));

---

Related Topics