none
Using .Count() for SELECT COUNT(*) RRS feed

  • Question

  • I have the following SQL code:

    CASE  
    WHEN wt.pmtlr_oi IS NULL OR (SELECT COUNT(*) FROM mc.SCHEDENTRY WITH (NOLOCK) WHERE wt_oi = wt.wtskoi AND shedref_oi NOT IN (SELECT shdoi FROM mc.SCHDULE WITH (NOLOCK) WHERE aenm LIKE '%Breakin%')) = 0 	AND wt.pmtlr_oi IS NULL		
    THEN 'Break-In' 
    ELSE 'Planned' 
    END AS schedule

    which I have translated into:

    select new {Schedule = wt.pmtlr_oi == null || (from sch in SCHEDENTRies where wt_oi == wt.wtskoi && !(from sc in SCHDULEs
    		where sc.Aenm.Contains("Breakin") select sc.Shdoi).Contains(shedref_oi)).Count() == 0 && wt.pmtlr_oi == null ? "Break-In" : "Planned"}); 

    The problem is that I get a "Statement Expected" error at the .Count(). 

    Intellisense doesn't give me an option after "Contains(shedref_oi))" which is probably why I am getting the error, but I can't figure out how to do the SELECT COUNT(*) if not with a .Count().  I tried putting it before the "Contains(shedref_oi))" but that gives me even more errors so that can't be the answer.

    I would appreciate it if someone could tell me where I am going wrong

    Wednesday, June 13, 2012 4:40 PM

Answers

  • made an example for you

    //2//shdoiList will return value array
    var shdoiList = from b in entity.SCHDULE where b.aenm.Contains("Breakin") select b.shdoi;
    //1//
    int count = (from c in entity.SCHEDENTRY where c.wt_oi = wt.wtskoi && !(shdoiList.Contains(shedref_oi)) select c).Count();
    
    //src 1
    (SELECT COUNT(*) FROM mc.SCHEDENTRY WITH (NOLOCK) WHERE wt_oi = wt.wtskoi AND shedref_oi NOT IN 
    		//src 2
    		(SELECT shdoi FROM mc.SCHDULE WITH (NOLOCK) WHERE aenm LIKE '%Breakin%'))
    
    
    

    The nature are a bit diff between SQL query and LINQ. You cannot directly transform some..

    Hope this helps.

    • Marked as answer by PullingMyHair Wednesday, June 13, 2012 6:01 PM
    Wednesday, June 13, 2012 5:47 PM
  • Thank you
    • Marked as answer by PullingMyHair Wednesday, June 13, 2012 6:01 PM
    Wednesday, June 13, 2012 6:01 PM

All replies

  • Hi,

    What I would like to suggest is, break down your linq into smaller pieces.

    e.g.

    var smaller =(from sc in SCHDULEs
    		where sc.Aenm.Contains("Breakin") select sc.Shdoi);

    Then you can see the more clear picture.

    Btw, there is a tool to transform sql script to linq,  http://www.sqltolinq.com/

    Cheer!

    Wednesday, June 13, 2012 4:53 PM
  • Hi,

    What I would like to suggest is, break down your linq into smaller pieces.

    e.g.

    var smaller =(from sc in SCHDULEs
    		where sc.Aenm.Contains("Breakin") select sc.Shdoi);

    Then you can see the more clear picture.

    Btw, there is a tool to transform sql script to linq,  http://www.sqltolinq.com/

    Cheer!

    I tried that and I run into the same problem.  For example, I can get this to work fine:

    var results =
    
        from sc in SCHDULEs
        where sc.Aenm.Contains("Breakin") 
        select sc.Shdoi).Count()
    
    results.Dump();

    It's when I try to add to the code that I run into problems.  I tried expanding the code like this:

    var results =
    
    	(from sch in SCHEDENTRies 
    	where wt_oi == wt.wtskoi 
    	&&
    	(from sc in SCHDULEs
    	where sc.Aenm.Contains("Breakin") 
    	select sc.Shdoi)).Count();
    
    results.Dump();

    I left out the NOT IN part just to make things a little less complicated but I still get "a query body must end with a select clause or group clause" error.  I thought it might have something to do with my parenthesis but that doesn't seem to matter one way or the other. 

    Thank you for the information on the LINQtoSQL conversion tool.  Unfortunately, I am unable to download it at the place I am doing my internship.

    Wednesday, June 13, 2012 5:15 PM
  • made an example for you

    //2//shdoiList will return value array
    var shdoiList = from b in entity.SCHDULE where b.aenm.Contains("Breakin") select b.shdoi;
    //1//
    int count = (from c in entity.SCHEDENTRY where c.wt_oi = wt.wtskoi && !(shdoiList.Contains(shedref_oi)) select c).Count();
    
    //src 1
    (SELECT COUNT(*) FROM mc.SCHEDENTRY WITH (NOLOCK) WHERE wt_oi = wt.wtskoi AND shedref_oi NOT IN 
    		//src 2
    		(SELECT shdoi FROM mc.SCHDULE WITH (NOLOCK) WHERE aenm LIKE '%Breakin%'))
    
    
    

    The nature are a bit diff between SQL query and LINQ. You cannot directly transform some..

    Hope this helps.

    • Marked as answer by PullingMyHair Wednesday, June 13, 2012 6:01 PM
    Wednesday, June 13, 2012 5:47 PM
  • Thank you
    • Marked as answer by PullingMyHair Wednesday, June 13, 2012 6:01 PM
    Wednesday, June 13, 2012 6:01 PM
  • Have you tried doing the queries separated and intersecting the results?

    Regards

    Wednesday, June 13, 2012 6:43 PM