LINQ– Expression Variable in Where Condition

 

 

public ActionResult IndexPerTypeId(int id, string type)
{
    Expression<Func<Transaction, bool>> lamda;

    switch (type)
    {
        case "Book": lamda = t => t.BookId == id; break;
        case "Category": lamda = t => t.CategoryId == id; break;
        case "Payee": lamda = t => t.PayeeId == id; break;
        case "Project": lamda = t => t.ProjectId == id; break;
        default: lamda = t => true == true; break;
    }

    var transcationset = db.TranscationSet
        .Where(lamda)
        .OrderByDescending(t => t.Date)
        .Include(t => t.Category).Include(t => t.Payee)
        .Include(t => t.Project).Include(t => t.Book);

    return PartialView("Index", transcationset.ToList());
}

DbContext APIs (for Insert, Update, Delete and Get)

The model of SalesOrder.edmx

SalesOrder.Context.cs

SalesOrderDBContext : DbContext

 

GetCustomers()

Context.Entity.ToList()

 

GetCustomer(int id)

Context.Entity.Find(primary_key)

TOP (2): to make sure Find() is always return 1 record, so it will raise an exception if SQL server return 2 records

 

InsertCustomer()

1. Context.Entity.Add(item)

2. Context.SaveChanges()

3. Able to get primary key after insertion

INSERT and SELECT are in a single transaction

1. For context.CustomerSet.Add(customer);

2. For customer.Id

 

UpdateCustomer()

SELECT and UPDATE are in different transactions

Hardcode name for WHERE condition

 

DeleteCustomers()

Context.Entity.Remove(item)

SELECT and DELETE are in different transactions

Query NuGet information by LINQPad

Step 1, In Chrome browser, open following link to LINQPad site

http://prod.roozz.com/apps/61/LINQPad.htm

 

Step 2, Add a connection to NuGet official source, select WCF Data Services 5.1 (OData 3) as driver, hit Next button

 

Step 3, Input NuGet URI, https://nuget.org/api/V2/, click Test button

 

Step 4, A message, Connection Successful, will popup if connection is ok

 

Step 5, Finally, the connection of NuGet official stie is created, and a Packages is ready for query.

 

Step 6, Choose the connection to NuGet

Step 7, Type LINQ expression (sample code in below)

Step 8, Click to Run

Step 9, See return results

Sample LINQ Expression:

Packages.OrderByDescending(x => x.LastUpdated)
	.Select(x =>
		new
		{
			Id = x.Id,
			Tags = x.Tags,
			Title = x.Title,
			Desc = x.Description,
			ProjectUrl = x.ProjectUrl,
			GalleryDetailsUrl = x.GalleryDetailsUrl,
			Version = x.Version,
			LastUpdated = x.LastUpdated
		}
	)

 

Step 10, Switch to Request Log, the url of query will show. We can copy the link and paste it in browser to see as well

 

Step 11, open in Chrome or IE browsers

Chrome

IE

Simple LINQ Examples

Example 1:

//Method 1
account.Where(m => m.Name.Contains("Cash"))
        .Where(m => m.No == "A123");

//Method 2
var query = account.Where(m => m.Name.Contains("Cash"));
query = query.Where(m => m.No == "A123");

Example 2: SQL In() and Any()

// Generate SQL IN()
int[] ids = new[] { 1, 3, 5, 7 };
account.Where(m => ids.Contains(m.Id));

// Generate SQL ANY()
int[] orders = new[] { 1, 3, 5, 7 };
account.Where(m => m.Orders.Any(i => orders.Contains(i.Id)));

Example 3: Count

account.Count(m => m.Id >= 100);
account.Where(m => m.Id >= 100).Count();

 

Example 4: Any

account.Any(m => m.Id >= 100);
account.Where(m => m.Id >= 100).Any();

Example 5: First

// Exception, if no record found
account.First(m => m.Name.Contains("Cash"));
// Null, if no record found
account.Where(m => m.Name.Contains("Cash")).FirstOrDefault();

Example 6: Single

// Exception, if record is not 1 (no record or more than 2)
account.Single(m => m.Name.Contains("Cash"));
// Exception, if record is more than 1
// Null, if no record found
account.Where(m => m.Name.Contains("Cash")).SingleOrDefault();