Linq to Sql problem

Question

I have a problem with linq to sql query. If someone will help me, I will pray :)

Here is my sql query:

SELECT Y.kullaniciAdi   
    ,COUNT(A.adim_id) AS EklenenAdimlar
  FROM [Adimlar] AS A   
   INNER JOIN Yazilimcilar AS Y ON Y.yazilimci_id = A.refYazilimci_id
   INNER JOIN Durumlar AS D ON D.durum_id = A.refDurum_id
   INNER JOIN AdimCalismalari AS AC ON AC.refAdim_id = A.adim_id
  WHERE MONTH(eklenmeTarihi) BETWEEN 3 AND 3
    AND DAY(eklenmeTarihi) BETWEEN 22 AND 29
    AND YEAR(eklenmeTarihi) BETWEEN 2011 AND 2011
    AND AC.refDurum_id IN (2,5)
  GROUP BY A.refYazilimci_id,Y.kullaniciAdi

Here is my linq to sql query:

var AdimCalismalari =
    from Adm in db.Adimlars.AsEnumerable()
     join Yzl in db.Yazilimcilars on Adm.refYazilimci_id equals Yzl.yazilimci_id
     join Drm in db.Durumlars on Adm.refDurum_id equals Drm.durum_id
     join AdmC in db.AdimCalismalaris on Adm.adim_id equals AdmC.refAdim_id
     where
         Adm.eklenmeTarihi.Value >= DateTime.Parse(dexIlkTarih.Text) &&
         Adm.eklenmeTarihi.Value <= DateTime.Parse(dexSonTarih.Text)
         && (AdmC.refDurum_id == 2 || AdmC.refDurum_id == 5)
     group Adm by new { yazilimci = Yzl.kullaniciAdi, AdimSayi = Adm.adim_id }
         into AdimToplam
         select new
                    {
                        yazilimci = AdimToplam.Key.yazilimci,
                        AdimSayi = AdimToplam.Key.AdimSayi
                    };

My linq query return null result however my sql query return 3 rows. Do you have any idea about my problem?

KR,

Çağın

Try using the app linqpad.net [linqpad]. It will try to convert your Linq into SQL so you can see exactly what your linq query means in SQL. — Hallaghan
– Hallaghan, Commented Mar 30, 2011 at 11:08
Try to break out the DateTime.Parse outside the query and make sure the dates are parsed correctly. — Albin Sunnanbo
– Albin Sunnanbo, Commented Mar 30, 2011 at 11:14
I changed those date time values instead DateTime.Parse("22.03.2011") and DateTime.Parse("29.03.2011"). It says, value can not be null. — cagin
– cagin, Commented Mar 30, 2011 at 11:22
To match your SQL query, can't you use Adm.eklenmeTarihi.Value.Month == 3 && Adm.eklenmeTarihi.Value.Day >= 22 && Adm.eklenmeTarihi.Value.Day <= 29? — Stephen Chung
– Stephen Chung, Commented Mar 30, 2011 at 11:30
Just noticed your group by columns are different. SQL has A.refYazilimci_id and Linq has Adm.adim_id — Nasmi Sabeer
– Nasmi Sabeer, Commented Mar 30, 2011 at 11:39

Daniel Hilgarth · Accepted Answer · 2011-03-30 11:18:28Z

3

I see one potential problem:
When dexIlkTarih.Text is 2011-03-22, dexSonTarih.Text is 2011-03-29 and Adm.eklenmeTarihi.Value is 2011-03-29 01:00, i.e. it has the same date as your upper bound but also a time, it will not be returned, because:

2011-03-29 = 2011-03-29 00:00 < 2011-03-29 01:00

This is a definite difference between your SQL and your LINQ query.

answered Mar 30, 2011 at 11:18

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stephen Chung Over a year ago

Too true. Too easy for people to forget that a date defaults to midnight on the morning side. +1

Daniel Hilgarth Over a year ago

It's a common pitfall that caught me more than once... So I am a bit sensible about it now ;-)

cagin · Accepted Answer · 2011-03-30 11:38:20Z

1

Silly me,

I delete .AsEnumerable() from Adimlars and it resolved. Thank you for suggestions.

KR,

Çağın

answered Mar 30, 2011 at 11:38

cagin

5,92816 gold badges81 silver badges133 bronze badges

Collectives™ on Stack Overflow

Linq to Sql problem

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related