Oğuz Yağmur'un Günlüğü

February 8, 2008

@ 03:07 PM

İki tabloyu birlikte sorgulamak için inner join kullanırız. Bunu Linq To Sql ile yaparken aşağıdaki gibi bir Linq Query yazarak çözüyoruz
(Kisi, Telefon ve Adres tablolarımız olduğunu, bir kişinin de birden çok adresi ve telefonu olduğunu düşünüyoruz )
Bu sorgu sonucunda bana kişiler tablosundaki isimler ve bunlara ait telefon numaraları gelecektir.

from kisi in Kisis
join tlf in Telefons
on kisi.ID equals tlf.KisiID
select new
{
Ad = kisi.Ad,
Tlf = tlf.Numara
}

T-Sql çıktısı
SELECT [t0].[Ad], [t1].[Numara] AS [Tlf]
FROM [Kisi] AS [t0]
INNER JOIN [Telefon] AS [t1] ON [t0].[ID] = [t1].[KisiID]

Lambda Expression
Kisis.Join ( Telefons, kisi => kisi.ID, tlf => tlf.KisiID, (kisi, tlf) => new { Ad = kisi.Ad, Tlf = tlf.Numara } )

Senaryomuzu değiştirelim ;
Kişiler tablosundaki tüm kayıtlat gelsin, telefon numarası olmayanlar da gelsin telefonu yoksa null değer gelsin. Bunun içinde Left Outer Join ile çözeriz. Linq sorugumuzu şu şekilde değiştirmemiz yeterli olacaktır.

from kisi in Kisis
join tlf in Telefons
on kisi.ID equals tlf.KisiID into data
from x in data.DefaultIfEmpty()
select new
{
Ad = kisi.Ad,
Tlf = x.Numara
}

T-SQL çıktısı

SELECT [t0].[Ad], [t1].[Numara] AS [Tlf]
FROM [Kisi] AS [t0]
LEFT OUTER JOIN [Telefon] AS [t1] ON [t0].[ID] = [t1].[KisiID]

Lambda Expression

Kisis
   .GroupJoin (
      Telefons, 
      kisi => kisi.ID, 
      tlf => tlf.KisiID, 
      (kisi, data) => 
         new  
         {
            kisi = kisi, 
            data = data
         }
   )
   .SelectMany (
      temp0 => temp0.data.DefaultIfEmpty (), 
      (temp0, x) => 
         new  
         {
            Ad = temp0.kisi.Ad, 
            Tlf = x.Numara
         }
   )

Categories: C# | Code

« LINQ to SQL ( DataContext.Log ) | Home | Çankaya Üniversitesinde Büyük Etkinliğim... »

Thursday, August 20, 2009 5:10:20 PM (GTB Daylight Time, UTC+03:00)

Selamlar

Entity Framework üzerinden Linq to Sql sorgusu yazıyorum
fakat Entity Framework DefaultIfEmpty() komutunu desteklemiyor.

Dolayısıyla inner join da problem yok fakat
left join olayını kullanamıyorum.

2 adet tablom var, seyahat (travel) ve konaklama(lodging).
Seyahat kaydı asıl kayıt, altına bir kaç tane konaklama kaydı girilebilir (yada girilmez), selectimde verdiğim kritere göre (kalınan otelin ülkesine göre) bu seyahat kayıtlarını bulmak istiyorum, başta join yapacağım sonrada sorgulayacağım...

var qSearch = from travel in entities.Travel
join kal in entities.Lodging on travel.Id equals kal.Travel.Id into tablo2
from kalma in tablo2.DefaultIfEmpty()
select new { travel, kalma};

qSearch = qSearch.Where(z=> z.kalma.Hotel.City.Country.Id == UlkeId);

sorgunun sonucunda...

+ base {"LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable`1[UI.Web.Transportation] DefaultIfEmpty[Transportation](System.Collections.Generic.IEnumerable`1[UI.Web.Transportation])' method, and this method cannot be translated into a store expression."} System.SystemException {System.NotSupportedException}

hatası alıyorum

Direk join diyerek into vs.. kullanmadığımda alt kayıtları(konaklama kayıtları) olmayan kayıtlar (seyahat kayıtları) gelmiyor.Nasıl bir çözüm önerirsiniz ?

Teşekkür ederim...

Kerem

Thursday, August 20, 2009 11:07:29 PM (GTB Daylight Time, UTC+03:00)

Problemi aşağıdaki gibi çözdüm, belki işinize yarar

var qSearch = from travel in entities.Travel

select new
{
travel,

konaklama = travel.Lodging.Select(ko =>
new { ko }),

};

if (UlkeId != 0)
{
qSearch = qSearch.Where(z => z.konaklama.All(t => t.ko.Hotel.City.Country.Id == UlkeId));
}

Kerem

Friday, October 16, 2009 8:20:33 AM (GTB Daylight Time, UTC+03:00)

<h2 align="center">Costs For Uggs--What It Costs?</h2>
-->Are you lusting after a few (or even more) <a href="http://www.goodugg.co.uk/ugg-classic-cardy-c-161.html">Ugg boots</a> and if so, you're in good company, as girls and women of all ages, especially with the simple, slipper-like design of these sheepskin <a href="http://www.goodugg.co.uk/grey-ugg-boots-5819-classic-cardy-p-23458.html">ugg boots sale </a>, which are relatively easy and very comfortable to wear. And they appear to be affected. Some men are also jumping on the trend and luxury UGGs? Australia, the brand also offers a range of contemporary styles for them.
-->Could you mind giving everyone loves from <a href="http://www.goodugg.co.uk/ugg-classic-tall-c-162.html">ugg classic </a>? There are so many types of uggs, <a href="http://www.goodugg.co.uk/ugg-bailey-button-c-178.html?zenid=847c0f495b87cb43928dcc19d09f4019">ugg bailey button</a>,ugg <a href="http://www.goodugg.co.uk/ugg-classic-tall-c-162.html">classic tall</a>, ugg <a href="http://www.goodugg.co.uk/ugg-classic-short-c-160.html">classic short</a>, ugg <a href="http://www.goodugg.co.uk/ugg-classic-cardy-c-161.html">classic cardy</a>. How to choose your favorite? Or do you really want to one uggs regardless its style? Despite their design is awkward and slipper-lile, <a href="http://www.goodugg.co.uk/">Uggs</a> is one of the few stations that are of general interest, have argued that cross generational lines.
-->Young people, students and young mothers and the Middle Ages, the original Black <a href="http://www.goodugg.co.uk/ugg-ultra-tall-c-167.html">Ultra Tall UGG Boots</a>, seem pulled the fleecy-lined boots that are manufactured in Australia, with the best materials. Are you sure that your feet warm in winter without socks, and cool in summer so that is more versatile too? If no, hurry up to take one ugg boots on <a href="http://www.goodugg.co.uk/ugg-classic-tall-c-162.html">uggs on sale</a> ! That's why we see people wear them in schools, supermarkets, on the slopes and even in the most popular beaches in the United States and abroad. Many surfers also use uggs to keep their feet warm.
-->What do you really care about? Is its price or quality, or you just following the general trend? You know what are you thinking in your heart!

angelia110 | sharing_09AT NOSPAMhotmail dot com

Saturday, October 24, 2009 3:42:06 PM (GTB Daylight Time, UTC+03:00)

why the ugg bailey <a href="http://www.linkboots.com/ugg-womens-knightsbridge-c-50.html">ugg knightsbridge sale</a> button boot is so popular? as we known,<a href="http://www.linkboots.com/ugg-womens-knightsbridge-c-50.html">ugg knightsbridge boots</a> have morphed together their two most popular styles. they started with the ever-popular <a href="http://www.linkboots.com/ugg-womens-knightsbridge-c-50.html">ugg knightsbridge sand</a> and then added a wooden <a href="http://www.linkboots.com/ugg-classic-cardy-5819-c-26.html">classic cardy uggs grey</a> from their best-selling, as seen on oprah, uggcardy. they are one pop-fusion <a href="http://www.linkboots.com/ugg-womens-knightsbridge-c-50.html">ugg knightsbridge black</a>. when the new fall step into our life this year, <a href="http://www.linkboots.com/ugg-womens-knightsbridge-c-50.html">ugg knightsbridge on sale</a> are beginning search for an footwear going together with their jeans. now you are reaching to the every <a href="http://www.linkboots.com/ugg-classic-cardy-5819-c-26.html">classic cardy ugg australia</a> for the latest fashion trend. in past two or<A href="http://www.thegoodboots.com/specials.html" target="_blank"> </A>three years, the <a href="http://www.linkboots.com/ugg-classic-cardy-5819-c-26.html">classic cardy uggs on sale</a> kept women, men and kids <a href="http://www.linkboots.com/ugg-womens-knightsbridge-c-50.html">ugg knightsbridge boot</a> salewarm and cozy under its super-popular fashion. <a href="http://www.linkboots.com/ugg-womens-knightsbridge-c-50.html">ugg knightsbridge chestnu</a><a href="http://www.linkboots.com/ugg-womens-knightsbridge-c-50.html">t</a> the new fall in 2009, we get <a href="http://www.linkboots.com/ugg-womens-knightsbridge-c-50.html">ugg knightsbridge uk</a>. the <a href="http://www.linkboots.com/ugg-womens-knightsbridge-c-50.html">knightsbridge uggs</a> become the first choice for ugg follower <a href="http://www.linkboots.com/ugg-womens-knightsbridge-c-50.html">ugg knightsbridge ugg boots</a> and getting hotter and hotter. the <a href="http://www.linkboots.com/ugg-womens-knightsbridge-c-50.html">ugg knightsbridge uggs on sale</a> style c the bailey button has become a must have this winter. now <a href="http://www.linkboots.com/ugg-bailey-button-5803-c-30.html">ugg australia bailey button</a> there are 5 colors available in 5 colors including black, chestnut, grey, chocolate and sand on <a href="http://www.linkboots.com/ugg-bailey-button-5803-c-30.html">ugg australia bailey button boot</a>. we provide enough stock for this fall and winter. you <A href="http://www.thegoodboots.com/ugg-classic-cardy-c-2.html" target="_blank">ugg shoes </A>wil enjoy shopping anytime before this <a href="http://www.linkboots.com/ugg-bailey-button-5803-c-30.html">ugg boots bailey button</a>. anyway, the popular classic styles including <a href="http://www.linkboots.com/ugg-bailey-button-5803-c-30.html">bailey button grey uggs</a>, ugg boot short (5825) and<a href="http://www.linkboots.com/ugg-classic-cardy-5819-c-26.html"> classic cardy ugg boots</a> are all your choices. tags ?

uggboots | linkbootsAT NOSPAMbazargo dot com