Lekérdezés szintaktitka cheat-sheet¶

Ebben a cheat-sheet-ben a különböző tanult nyelvek lekérdezési szintaktikája kerül összehasonlításra.

Warning

C# LINQ használatakor ne felejtsük el példányosítani az adatbázis kontextust egy using block-ban.

using (var db = new AdatvezDbContext())
{
    //lekérdezések, stb...
}

SELECT¶

SQL:

SELECT column1, column2
FROM table
WHERE condition;

C# LINQ:

// fluent szintaktika
var query = context.Table
    .Where(item => condition)
    .Select(item => new { item.Column1, item.Column2 });

// Query szintaktika           
var query = from item in context.Table
            where condition
            select new { item.Column1, item.Column2 };

Megjegyzés

Select()-ben névtelen objektumot hoztunk létre, ennek property-jei szabadon választhatóak, a vetítési művelet elvégzéséhez.

Warning

Ha szükségünk van a navigation propertykkel hivatkozott entitásokra, akkor a fejlesztő a kódban ezt specifikálja az Include használatával, és a rendszer betölti a kért hivatkozásokat is.

var prod = db.Products
    .Include(p => p.VAT)
    .Where(p => p.ID == 23)
    .SingleOrDefault();
if(prod is not null)
{
    Console.WriteLine(prod.VAT.ID);
}

C# MongoDb:

MongoDb-beli Project()-et hasonlóképpen használjuk a LINQ-beli Select()-hez, amennyiben vagy vetíteni akarunk bizonyos attribútumokra.

collection
    .Find(item=>item.Column1 == value)
    .Project(item => new
    {
        Attr1 = item.Coloumn1,
        Attr2 = item.Coloumn2
    })
    .ToList();

WHERE¶

SQL:

SELECT *
FROM table_name
WHERE column_name = "Example";

Lekérdezés melyben egy mező NULL értékű:

SELECT *
FROM table_name
WHERE column_name IS NULL;
-- Ha ellenkezőjére lennénk kíváncsiak, IS NOT NULL kellene

ISNULL függvény: Ha van érték a salary oszlopban, akkor annak az értékével dolgozik tovább a lekérdezés, ha nem akkor a második paraméterként kapott értékkel (ebben az esetben 0).

SELECT employee_id, employee_name, ISNULL(salary, 0) AS modified_salary
FROM employees;

Allekérdezést el lehet nevezni, eredményeire lehet hivatkozni. Látható az alábbi feladatbeli példában (feladat a Microsoft SQL gyakorlatról).

Feladat: Melyik termék kategóriában van a legtöbb termék?

SELECT TOP 1 Name, (SELECT COUNT(*) FROM Product WHERE Product.CategoryID = c.ID) AS cnt
FROM Category c
ORDER BY cnt DESC

C# LINQ:

Feladat: Hol kisebb az ár mint 1000?

//Fluent szintaktika
db.Products.Where(p => p.Price < 1000)

//Query szintaktika
from p in db.Products
where p.Price < 1000

C# MongoDb:

Warning

MongoDb-nél a Find-ban olyan feltétel lehet csak, ami a dokumentumra vonatkozik, nem lehet csatolt (joinolt) másik gyűjteményre hivatkozni!

Warning

MongoDb-nél mindig oda kell írni egy .Find()-ot a parancsokba (amennyiben nem használunk Aggregate()-et), így gyakori a .Find(_=>true) paraméterezésű Find() parancs.

Note

A Find() függvény eredménye még nem az eredményhalmaz, hanem csak egy leíró a lekérdezés végrehajtásához. A ToList() kéri le a teljes eredményhalmazt listaként. (Amennyiben egyetlen elemet szeretnénk visszaadni nem ezt a függvényt kell használni, lásd lentebb)

collection.Find(item => item.Column == value).ToList();

Egy elemű eredményhalmaz és eredményhalmaz számossága¶

SQL:

Azokban az esetekben, amikor csak egyetlen sort szeretnénk visszakapni lekérdezéseinkből.

SELECT TOP 1 *
FROM Product
ORDER BY Name

C# (LINQ és MongoDb):

Amennyiben csak az első elemre van szükségünk, vagy tudjuk, hogy csak egy elem lesz, akkor használhatjuk a .First(), .FirstOrDefault(), vagy .Single(), .SingleOrDefault() függvényeket. Fontos, hogy .Single() vagy .SingleOrDefault() függvények használata esetén gondoskodjunk arról, hogy a megelőző lekérdezés valóban egyetlen adatelemet adjon vissza. (Különben kivételt fog dobni)

Lehetőségünk nyílik lapozás használatára is .Skip() használatával

C# LINQ:

Take() használatával korlátozhatjuk hány eredményt ad vissza a lekérdezés.

// 10-et olvas ki
db.Products.Take(10)

// Skippel 10-et majd kiolvas 10-et
db.Products.Skip(10).Take(10)

C# MongoDb:

Limit() használatával korlátozhatjuk hány eredményt ad vissza a lekérdezés.

//10-et olvas ki
collection.Find(...).Limit(10);
//Skippel 10et majd kiolvas 10-et
collection.Find(...).Skip(10).Limit(10);

Group By¶

SQL:

Listázza ki az M betűvel kezdődő termékek nevét és a megrendelt mennyiségeket úgy, hogy azok a termékek is benne legyenek a listában melyekből nem rendeltek meg semmit

SELECT p.Name, SUM(oi.Amount)
FROM Product p
     LEFT OUTER JOIN OrderItem oi ON p.id = oi.ProductID
WHERE p.Name LIKE 'M%'
GROUP BY p.Name

C# LINQ:

Listázzuk ki a termékeket VATID szerint csoportosítva.

// Fluent szintaktika
db.Products.GroupBy(p => p.VATID)

// Query szintaktika
from p in db.Products
group p by p.VATID

C# MongoDb:

collection.Aggregate()
    .Match(Builders<Product>.Filter.AnyEq(x => x.Categories, "Labdák")) // szűrés
    .Group(x => x.VAT.Percentage, x => x) // csoportosítás
    .ToList()

A Match()-en belül lambda helyett a feltételnek, egy alternatív megadását láthatjuk. Ide több különböző kulcsszót lehet írni és egymásba lehet ágyazni a Filtereket.

collection.Find(x => x.Price == 123);
collection.Find(Builders<Product>.Filter.Eq(x => x.Price, 123)); //Eq, mint equals

collection.Find(x => x.Price != 123);
collection.Find(Builders<Product>.Filter.Ne(x => x.Price, 123)); // Ne, mint not equals

collection.Find(x => x.Price >= 123);
collection.Find(Builders<Product>.Filter.Gte(x => x.Price, 123)); // Gte, mint greater than or equal to

collection.Find(x => x.Price < 123);
collection.Find(Builders<Product>.Filter.Lt(x => x.Price, 123)); // Lt, mint less than

collection.Find(x => x.Price < 500 || x.Stock < 10);
collection.Find(
    Builders<Product>.Filter.Or(
        Builders<Product>.Filter.Lt(x => x.Price, 500),
        Builders<Product>.Filter.Lt(x => x.Stock, 10)
    )
);

collection.Find(x => !(x.Price < 500 || x.Stock < 10));
collection.Find(
    Builders<Product>.Filter.Not(
        Builders<Product>.Filter.Or(
            Builders<Product>.Filter.Lt(x => x.Price, 500),
            Builders<Product>.Filter.Lt(x => x.Stock, 10)
        )
    )
);

Példa .Group() második paraméterében lehet vetítéseket megadni hasonlóan a .Project() függvényhez.

var r = productsCollection
    .Aggregate()
    .Group(
        // csoportosítás rész
        p => p.VAT.Percentage,
        // projekcios rész
        p => new
        {
            VatPercentage = p.Key
            SumPrice = p.Sum(s => s.Price)
        })
    .ToList();

Order By¶

SQL:

ORDER BY column1 ASC, column2 DESC;

C# LINQ:

Kétszintű rendezés

items.OrderBy(item => item.Column1)
     .ThenByDescending(item => item.Column2)

C# MongoDb:

itemcollection.Sort(
    Builders<CollectionItem>.Sort.Ascending(item => item.Coloumn))

JOIN¶

SQL:

SELECT table1.column, table2.column
FROM table1
JOIN table2 ON table1.column = table2.column;

C# LINQ:

Mivel navigation propertyk segítségével általában elérhetőek az asszociált osztályok, explicit illeszteni csak akkor kell, ha a feladat megoldási logikája megkívánja vagy nincs navigation property az egyik osztályban a másikra.

// Fluent szintaktika
var query = context
    .Table1
    .Join(context.Table2,
        item1 => item1.Column,
        item2 => item2.Column,
        (item1, item2) => new { item1.Column, item2.Column });

// Query szintaktika
var query = from item1 in context.Table1
            join item2 in context.Table2
            on item1.Column equals item2.Column
            select new { item1.Column, item2.Column };

C# MongoDb:

MongoDb-ben nem tanultuk szerveri oldari joinra módszert. LINQ segítségével van az után, hogy a teljes illesztendő adathalmazokat kiolvastuk az adatbázisból. Az adatok lekérdezése után általában a .ToHashSet() és a .Contains() metódusok segítségével, kliens oldali dictionary készítéssel végezzük el az illesztést. (Lásd MongoDb gyakorlat 1. Feladat 5. pontja)

Distinct¶

SQL:

SELECT DISTINCT p.Name
FROM Product p

C# LINQ:

Feladat: Minden különböző termék név

db.Products.Select(p => p.Name).Distinct();

C# MongoDb: Feladat: Minden CategoryID, melyhez tartozik termék, melynek ára nagyobb mint 3000.

var xd = productsCollection
    .Distinct(p => p.CategoryID, p => p.Price > 3000)
    .ToList();

Oszlopfüggvények¶

SQL:

Feladat: Mennyibe kerül a legdrágább termék?

SELECT MAX(Price)
FROM Product

Feladat: Melyek a legdrágább termékek?

SELECT *
FROM Product
WHERE Price = (SELECT MAX(Price) FROM Product)

C# LINQ:

db.Products.Count()

// Alábbihoz hasonló képpen Max, Min, Sum
db.Products.Select(p => p.Price).Average()

C# MongoDb:

Az általános aggregációkhoz készíthetünk pipeline-okat. Egy pipeline képes több eredményt is visszaadni egy dokumentumhalmazról (pl. total, average, maximum vagy minimum értékeket).

Maximum függvény:

Tip

Figyeljük meg a Group-on belüli konstans szerinti csoportosítást, mely azért van hogy a teljes collectionre számítsuk ki az oszlopfüggvény értékét.

collection
    .Aggregate()
    .Group(p => 1, p => p.Max(x => x.Stock))
    .Single();

Nézzük ezt a csoportosítás példáján keresztül. Alábbi lekérdezés kilistázza, hogy mely rendeléshez, mekkora összértékben tartoznak OrderItem rekordok ha azok összértéke meghaladja a 30000-et.

var q = ordersCollection
    .Aggregate()
    .Project(order => new
    {
        CustomerID = order.CustomerID,
        Total = order.OrderItems.Sum(oi => oi.Amount * oi.Price)
    })
    .Match(order => order.Total > 30000)
    .ToList();

Delete¶

SQL:

DELETE
FROM Product
WHERE ID = 23

C# LINQ:

using (var db = new AdatvezDbContext())
{
    var deleteThis = db.Products
        .Select(p => p.ID == 23)
        .SingleOrDefault();
    if(deleteThis is not null)
    {
        db.Products.Remove(deleteThis);
        db.SaveChanges();
    }
}

C# MongoDb:

var deleteResult = collection.DeleteOne(x => x.Id == new ObjectId("..."));

Használd a DeleteMany parancsot ha több rekordot szeretnél törölni.

Insert¶

SQL:

INSERT INTO Product
VALUES ('aa', 100, 0, 3, 2, null)

Amikor másik lekérdezés eredményét szeretnénk beilleszteni:

INSERT INTO Product (Name, Price)
SELECT Name, Price
FROM InvoiceItem
WHERE Amount>2

C# LINQ:

using (var db = new AdatvezDbContext())
{
    db.Table.Add(new dataItem { Name = "Example" });
    db.SaveChanges();
}

C# MongoDb:

var newProduct = new Product
{
    Name = "Alma",
    Price = 890,
    Categories = new[] { "Gyümölcsök" }
};
collection.InsertOne(newProduct);

Update¶

SQL:

Feladat: Emelje meg azon termékek árát 10%-al, melyek nevében szerepel a "Lego" szó!

UPDATE Product
SET Price=1.1 * Price
WHERE Name LIKE '%Lego%'

Ha olyan értékeket szeretnénk adni a SET parancsban, melyek másik táblákból nyeretők ki, az alábbi képpen lehetséges. A példa az MS SQL gyakorlat anyagából van.

Feladat: A 9-es azonosítójú számú megrendelés státusz állapotát másoljuk be minden olyan OrderItem-be, mely hozzá tartozik.

UPDATE OrderItem
SET StatusID = o.StatusID
FROM OrderItem oi
INNER JOIN Order o ON o.Id = oi.OrderID
WHERE o.ID = 9;

C# LINQ:

Warning

Az Entity Framework gyakorlat 3. feladatának megoldása

Feladat: Írj olyan LINQ-ra épülő C# kódot, amely az "LEGO" kategóriás termékek árát megemeli 10 százalékkal!

using (var db = new AdatvezDbContext())
{
    var legoProductsQuery = db.Products
        .Where(p => p.Category.Name == "LEGO")
        .ToList();
    foreach (var p in legoProductsQuery)
    {
        p.Price = 1.1m * p.Price;
    }
    db.SaveChanges();
}

C# MongoDb:

Egy elem frissítése:

collection.UpdateOne(
    filter: x => x.Id == new ObjectId("..."),
    update: Builders<Product>.Update.Set(x => x.Stock, 5));

Jól látható, hogy az Update után a Filterhez hasonlóan több különböző operátor írható. Ilyenek a: Set, UnSet, SetOnInsert, CurrentDate, Mul, Min, Max, AddToSet (Teljes, részletes leírás a jegyzetben)

Minden 13-as kategória Id-jú kategória beli elem frissítése:

productsCollection.UpdateMany(
    filter: p => p.CategoryID == 13,
    update: Builders<Product>.Update.Mul(p => p.Price, 1.1));

Amennyiben úgy akarunk keresni, hogy vagy updatelunk egy bizonyos filterre illeszkedő elemet vagy ha nincs ilyen beillesztjük, használhatjuk az alábbi IsUpsert függvényt.

var catExpensiveToys = categoriesCollection.FindOneAndUpdate<Category>(
    filter: c => c.Name == "Expensive toys",
    update: Builders<Category>.Update.SetOnInsert(c => c.Name, "Expensive toys"),
    options: new FindOneAndUpdateOptions<Category, Category> { IsUpsert = true, ReturnDocument = ReturnDocument.After });

Összefoglaló táblázat¶

SQL	C# LINQ	C# MongoDb
`SELECT`	`Select()`	`Project()`
`WHERE`	`Where()`	`Find()`
`GROUP BY`	`GroupBy()`	`Group()`
`ORDER BY`	`OrderBy()`	`Sort()`
`JOIN`	Használj navigációs propertyket, ha lehetséges, különben: `Join()`	`Join()`
`DISTINCT`	`Distinct()`	`Distinct()`
`Count()`, `Max()`, `Average()`	`Count()`, `Max()`, `Average()`	Először `.Aggregate()`, majd: `Count()`, `Max()`, `Average()`
`DELETE FROM`	`.Remove()`, és `db.SaveChanges()` mentéshez	`.DeleteOne()`, `.DeleteMany()`
`UPDATE ... SET`	Módosítsd az adatokat, majd `db.SaveChanges()`	`.UpdateOne()`, `.UpdateMany()`
`INSERT INTO`	`.Add()` és aztán `db.SaveChanges()`	`.InsertOne()`, `.InsertMany()`

2023-11-13 Szerzők