Zapytanie SQL -> LINQ

Witam, jestem w trakcie pisania prostej strony internetowej w ASP.NET MVC2 i do komunikacji aplikacja -> baza danych używam EF. Jako, że EF to i oczywiście dane pobieramy przez LINQ. Nie wiem który dział jest odpowiedni czy C# czy Webmastering...

Zacznę od przedstawienia struktury bazy danych, niestety nie jest ona moim tworem i gratulacje temu, kto ją projektował :P. Jako, że dopiero zaczynam pisanie aplikacji webowych i poznaję sam język sql (w sumie to z sql dziś obejrzałem tylko serię video tutoriali z uw****.org - bez reklamy ;) ) itd to mam troszkę kłopotu.
Całe 40 min spędziłem na wymyśleniu zapytania SQL, aby odczytać to co potrzebuję... no ale nie o tym.
Ps nie mam dostępu do zapytań utworzonych przez kogoś, mam tylko kopię bazy.

ogólnie są 3 tabele : (opiszę tylko pola które potrzebuję wykorzystać)
Clients

• ClientID
• Name
• Surname

Product

• ProductID
• Price

Buys

• id
• buyNumber
• productID
• productCount
• ClientID
• Date
• isRealised

Tabele Client i Product są przejrzyste, ale Buys nie do końca (przynajmniej dla mnie). Niżej przykładowe dane w niej zawarte, aby łatwiej zrozumieć

Ja z tej tabeli chciałem uzyskać, imię i nazwisko klienta, buyNumber, oraz sumę wszystkich produktów bez i z vatem. Zapytanie wykonałem w następujący sposób. Jako, że chcę się nauczyć SQL napisałem to zapytanie w SQL i chciałem przenieść je do LINQ, ale tutaj nastąpiły kłopoty...

 
SELECT
	Buys.buyNumber, Buys.Date, Client.Name, Client.Surname, 
	SUM(ROUND(Product.Price * Buys.productCount,2)) as "Kwota Netto",
	SUM(ROUND((Product.Price * 1.22) * Buys.productCount,2)) as "Kwota Brutto"
FROM 
	Buys JOIN Client  ON Buys.ClientID  = Client.ClientID
	     JOIN Product ON Buys.productID = Product.ProductID
WHERE
	Buys.isRealised = 0 
	AND 
	Buys.buyNumber IN (SELECT DISTINCT Buys.buyNumber FROM Buys WHERE Buys.isRealised = 0)
GROUP BY 
	Buys.buyNumber, Buys.Date, Client.Name, Client.Surname

W linq spotkałem się z paroma kłopotami. Pierwszy to błędy przy paru GROUP BY. W sumie nie wiem jak użyć, nigdzie nie znalazłem grupowania po paru elementach :/ Drugim to **WHERE **... IN. Aby to uzyskać znalazłem tylko taki sposób :

 
//id są w long
            List<long> resultBuyNumbers = (from buys in DB.Buys 
                                   where buys.isRealised == false 
                                   select buys.buyNumber).Distinct().ToList();

//a w kodzie LINQ

from buys in DB.Buys
join client in DB.Clients on buys.ClientID equals client.ClientID
join product in DB.Products on buys.productID equals product.ProductID
where buys.isRealised == false && 
resultOne.Contains(buys.buyNumber) // co też niweluje opcję wszystko w jednym zapytaniu

<------------------------------------------------------------ EDYCJA EDYCJA -------------------------------------------------------->

Problem rozwiązany, w sumie kłopot był w zmęczeniu, i jako początkujący namieszałem sobie trochę w głowie i zapomniałem w ogóle chyba po co EF mapuje również powiązania między tabelami :/. Opiszę rozwiązanie, gdyż może komuś kiedyś się przyda.

Zacząłem od tego, że postanowiłem, że jeśli nie mogę zrobić tego w taki sposób zrobię to w inny. Zacząłem znów od napisania zapytania SQL - ach ta nauka, to również wybiło mnie z rytmu. Nieważne wystukałem taki, znacznie bardziej przyjemny kod SQL

 
SELECT 
	Buys.buyNumber, Buys.Date ,ROUND(Product.Price * Buys.productCount,2) as "CenaIlosc", Round((Product.Price * 1.22)*Buys.productCount,2) as "CenaVatIlosc",
	Client.Name, Client.Surname
FROM Buys 
	JOIN Product ON Buys.productID = Product.ProductID 
	JOIN Client ON Buys.ClientID = Client.ClientID
WHERE 
	Buys.isRealised = 0

Kłopot był taki, że wyniki mi się powtarzały, ale myślę a co tam, najwyżej resztę zrobię w zwykłym "forze" i tak też postanowiłem. Zapytanie przerobione na linq brzmiało :

            List<BuyListViewModel> result =
                         (from buys in DB.Buys
                          join product in DB.Products on buys.productID equals product.ProductID
                          join client in DB.Clients on buys.ClientID equals client.ClientID
                          where buys.isRealised == false
                          select new BuyListViewModel
                          {
                              BuyID = buys.buyNumber,
                              ClientName = client.Name,
                              ClientSurname = client.Surname,
                              NettoPrice = (float)Math.Round(product.Price * buys.productCount, 2),
                              BruttoPrice = (float)Math.Round((product.Price * 1.22) * buys.productCount, 2)
                          }).ToList();
 

Następnie forem wybrałem wyniki

 
List<BuyListViewModel> lista = new List<BuyListViewModel>();
            
            
            if (result.Count >= 1)
            {
                // 0 element list = 0 element resultu
                // lista.Count = 1
                lista.Add(result[0]);

                for (int i = 1; i < result.Count; i++)
                {
                    //jeśli result[aktualny] == result[poprzedni]
                    if(result[i].BuyID == result[i-1].BuyID)
                    {
                        //pobieramy ostatni element na liście i dodajemy do niego aktualny result
                        lista[lista.Count - 1].NettoPrice += result[i].NettoPrice;
                        lista[lista.Count - 1].BruttoPrice += result[i].BruttoPrice;
                    }
                    else if (result[i].BuyID != result[i-1].BuyID)
                    {
                        // w przeciwnym wypadku dodajemy nowy result
                        lista.Add(result[i]);
                    }

                }
            }
            return lista;

i byłem zadowolony, działało jak powinno. Po ok 30 min pisania całkiem innego kodu, zaświeciła mi się żaróweczka. Właśnie przez tyle głupot które popełniłem i przez tą całą naukę SQL zapomniałem, że EF bardzo ładnie sam zarządza relacjami i gdy dostałem się do tabeli Buys automatycznie dostałem referencję do obiektu w Product i Client, więc po co mam robić skomplikowane zapytanie, więc przerobiłem powyższy kod i wygląda następująco, nie mówię, że jeszcze nie można go zoptymalizować, ale na razie działa.

 
 List<Buy> nonRealisedList = DB.Buys.Where(b => b.isRealised == false).ToList();
            List<BuyListViewModel> lista = new List<BuyListViewModel>();

            if (nonRealisedList.Count >= 1)
            {
                BuyListViewModel buy =
                new BuyListViewModel(nonRealisedList[0].buyNumber, nonRealisedList[0].Client.Name,
                nonRealisedList[0].Client.Surname, (float)Math.Round(nonRealisedList[0].productCount * nonRealisedList[0].Product.Price, 2),
                (float)Math.Round(nonRealisedList[0].productCount *( 1.22 * nonRealisedList[0].Product.Price), 2));
                lista.Add(buy);

                for (int i = 1; i < nonRealisedList.Count; i++)
                {
                    if (nonRealisedList[i].buyNumber == nonRealisedList[i - 1].buyNumber)
                    {
                        //pobieramy ostatni element na liście i dodajemy do niego aktualny result
                        lista[lista.Count - 1].NettoPrice += (float)Math.Round(nonRealisedList[i].productCount * nonRealisedList[i].Product.Price,2);
                        lista[lista.Count - 1].BruttoPrice += (float)Math.Round(nonRealisedList[i].productCount * (nonRealisedList[i].Product.Price *1.22), 2);
                    }
                    else if (nonRealisedList[i].buyNumber != nonRealisedList[i - 1].buyNumber)
                    {
                        BuyListViewModel buyss =
                        new BuyListViewModel(nonRealisedList[i].buyNumber, nonRealisedList[i].Client.Name,
                        nonRealisedList[i].Client.Surname, (float)Math.Round(nonRealisedList[i].productCount * nonRealisedList[i].Product.Price, 2),
                        (float)Math.Round(nonRealisedList[i].productCount * (1.22 * nonRealisedList[i].Product.Price), 2));
                        lista.Add(buyss);
                    }

                }
            }

            return lista;

Mimo wszystko dziękuję i przepraszam za zaśmiecanie forum postem ;) Wybiła 00:10, czas trochę się zdrzemnąć

Jesteś pewien, że użycie typu float do obliczeń finansowych to dobry pomysł?

więc po co mam robić skomplikowane zapytanie
Po to, że SQL i LINQ właśnie do tego służą – by zapytać dokładnie o to, o co chcemy zapytać. Podejście typu „selekt gwiazdka” do tablicy i grzebanie po tym ręczne to zaprzeczenie idei SQL-a.

Co do float... Strona jest robiona raczej do nauki. Wartości w "sklepie" będą z przedziału ok 0.00 - 500.00 Jakoś nie obawiam się wkradnięcia błędu w float. Cena końcowa jest raczej tylko do podglądu całej sytuacji. Być może powinienem zmienić na double, jeśli tak - tak zrobię, chętnie poprawię jeśli trzeba, w końcu po to jest nauka.

Co do zapytania tak, jeszcze wyjdzie na to, że opcja pierwsza z działających będzie najlepsza. Na szybko zmierzyłem czas wykonywania metody (mierzyłem za pomocą klasy Stopwatch(), w celach testowych wzięło udział (wiem mało) 10 rekordów. W tym 5 unikalnych i 5 powtarzających się )

1. zapytanie linq i zsumowanie forem wszystkich powtarzających się wpisów : 186 ms
2. zapytanie linq pobierające wszystkie elementy z buy, oraz odwoływanie się do wartości tabel przez referencje w EF : 568 ms

W sumie różnica spora, jakby nie patrzeć ;)

Być może powinienem zmienić na double

Do bardzo dokładnych obliczeń dziesiętnych służy typ decimal - zajmuje 128 bitów (!) i prowadzi obliczenia nie w systemie dwójkowym ale dziesiętnym z bardzo dużą precyzją. Użycie float w czymś co służy tylko do nauki nie jest oczywiście jakimś strasznym błędem ;) . Ale zawsze warto wiedzieć.