Querying Entity Framework via Lambda Expression
In this article, we will write very flexible and easy queries using Lambda expression via Entity Framework. In today’s software world, as database programming is promoted to the external layer, the technologies we write queries over the organs become very important. Due to the rapid development of Entity Framework technology and the flexible dominance over Lamda expression objects, it began to be written on objects that are fairly easy to understand. Contrary to what you might think, these queries have very little performance loss with a good EF configuration (include use etc.).
We will use the Northwind db in queries. The inquiries and explanations are as follows.
Querying Entity Frame Work
- Assign all the data in the table db to the product table
|
2 |
List<Product> plist = db.Products.ToList(); |
- List products by name and assign to list
|
2 |
List<Product> plist2 = db.Products.OrderBy(p => p.ProductName).ToList(); |
- Products listed by name reverse printing
|
2 |
List<Product> plist3 = db.Products.OrderByDescending(p => p.ProductName).ToList(); |
- List the products tersten and catch the top 5 products and assign them to the list
|
2 |
List<Product> plist4 = db.Products.OrderByDescending(p => p.ProductName).Take(5).ToList(); |
- Capture product with id si 5
|
2 |
Product prdc = db.Products.FirstOrDefault(p => p.ProductID == 5); |
- Give the name of the product with ID 3
|
2 |
string name = db.Products.FirstOrDefault(p => p.ProductID == 3).ProductName; |
- List of products with Category ID 5
|
2 |
List<Product> plist5 = db.Products.Where(p => p.CategoryID == 5).ToList(); |
- Sort products with Category ID si 3 and Supplier ID si 2 by name Receive the first 5 record
|
2 |
List<Product> plist6 = db.Products.Where(p => p.CategoryID == 3 && p.SupplierID == 2).OrderBy(p => p.ProductName).Take(5).ToList(); |
- Productname a list of products with a letter
|
2 |
List<Product> plist7 = db.Products.Where(p => p.ProductName.Contains("a")).ToList(); |
- List of items STARTING with a letter of product name
|
2 |
List<Product> plist8 = db.Products.Where(p => p.ProductName.StartsWith("a")).ToList(); |
- Productname list of products ending with a letter
|
2 |
List<Product> plist9 = db.Products.Where(p => p.ProductName.EndsWith("a")).ToList(); |
- Do you have a category
|
2 |
bool isThere = db.Categories.Any(); |
- Category Is there a category with ID 5
|
2 |
bool isThere2 = db.Category.Any(c => c.CategoryID == 5); |
- Do you have a product that contains ‘taha’ in its name? Does not matter
|
2 |
bool isThere3 = db.Product.Any(p => p.ProductName.Contains("taha")); |
- Go to product line
|
2 |
Product[] pArray = db.Products.ToArray(); |
- Number of products
|
2 |
int piece = db.Products.Count(); |
- The number of Quantity Per Units in the product table (if column is null this column will not be added)
|
2 |
int piece2 = db.Products.Count(p => p.QuantityPerUnit != null); |
- Total of product prices on product table
|
2 |
decimal? totalPrice= db.Products.Sum(p => p.UnitPrice); |
- The most expensive product
|
2 |
decimal? expensiveProduct= db.Products.Max(a => a.UnitPrice); |
- Skip the first 5 products when sorted by name and list remaining products
|
2 |
List<Product> plist10 = db.Products.OrderBy(p => p.ProductName).Skip(5).ToList(); |
- Skip the first 5 products when sorted by name list 10 products
|
2 |
List<Product> plist11 = db.Products.OrderBy(p => p.ProductName).Skip(5).Take(10).ToList(); |
- order specific to the shipcountry column in the order table (sql “select distinct o.ShipCountry // from Orders o”)
|
2 |
List<string> countrylist = db.Orders.Select(a => a.ShipCountry).Distinct().ToList(); |
- Returns the value of the product in the primary key field in the product table (returns productID // value 5)
|
2 |
Product prdct = db.Products.Find(5); |
if you have question do not forget to write from the chat button next to it or from the comment
Thanks bro, this entity-framework is boolshit but i had to use it sometimes. Whatever, thanks again.
Thanks for you comment 🙂
Bir sorum olacak code first ile oluşturduğum bir db var elimde ve
Product.cs
public int Id { get; set; }
public string Name { get; set; }
public Category Category { get; set; }
Şeklinde tanımlı ve Category.cs
public int Id { get; set; }
public string Name{ get; set; }
public List Product{ get; set; }
şeklinde tanımlı. Ben Products’ları
context.Products.ToList();
bu şekilde çektiğimde Category kısımları boş geliyor. Bunu en düzgün yoldan nasıl çekerim. İki tabloyuda ayrı ayrı çekip birbirlerine eşitleyebilirim fakat benim merak ettiğim Product.cs ‘de Category Category property’si tanımladık bunu kullanarak kolay bir şekilde çekmemin bir yolu vardır diye düşünüyorum. Umarım derdimi anlatabilmişimdir.
Bu linki inceleyebilirsin https://docs.microsoft.com/en-ca/ef/ef6/querying/related-data
problemi çözdüm
return context.Uruns.Include(“Category”).ToList();
deyip Category.cs’de de
public override string ToString()
{
return Name;
}
dediğim zaman geliyor.
Ellerinize sağlık 🙂