none
LINQ to DATATABLE -> HOW TO Embedded Select RRS feed

  • Question

  •  

    How would you translate this into a LINQ query ? 

    This Include (embedded Select statement and also conditional where séparate with  "IF")

    You can answer in C# or VB.net 

    Thanks

    If lngFrom <> 0 Or lngTo <> 0 Then
            s = "select (select count(*) from worksheet as w2 where w2.Rack = w.Rack and w2.LineNumber <= w.LineNumber"
           If minWeight > 0 Then
                If intUSeSampleWeight = 1 Then
                    s = s & " and w2.SampleWeight >= " & minWeight
                Else
                    s = s & " and w2.ProductWeight >= " & minWeight
                End If
            End If
            If strExcludeSampleText <> "" Then
                s = s & " and w2.SampleID not like '*" & strExcludeSampleText & "*'"
            End If
            s = s & ") as RackLine from worksheet as w where w.LineNumber = " & lngCurrLine

    Tuesday, May 8, 2012 5:35 PM

Answers

  • //INITIALIZE THE QUERY Variable TO A DEFAULT VALUE
    var Query = from w in DtWorkSheet.AsEnumerable()
                select w;
    
    IF(true)
    {
    //OVERWRITE THE VALUE OF THE Variable Query
    Query = from w in DtWorkSheet.AsEnumerable()
         where w.Field<int>("LineNumber") == lngCurrLine
         select new 
         { 
          RackLine = (int)(from w2 [DaTable].AsEnumerable()
           where w2.Field<int>("val") == w.Field<int>("val") 
           && w2.Field<int>("val2") <= w.Field<int>("val2") 
           && w2.Field<int>("SampleWeight") >= minWeight 
       && SqlMethods.Like(w2.Field<string>("D"), "%" + strExcludeSampleText + "%")select w2).Count()
          };
    }
    
    
    //Get the value of Query 
    foreach( var q in Query)
    {
      MessageBox.Show(q.RackLine)
    }

    I repeated the code in the different (condition- IF)

    I also notice there was a "Like" statement which can be translate into LINQ using " SqlMethods.Like "  need to add a reference to your project.

    ** I wrote the code as exemple it has not been tested and I made some change to shorten the code this might now compile in the actual state. That might help someone a lot there is a lot of research into it. Not easy to find on google.

     
    • Marked as answer by Benoit Bolduc Thursday, May 10, 2012 7:32 PM
    Thursday, May 10, 2012 7:31 PM

All replies

  • I found part of the solution here's how to embeded Select into LINQ

      
    var Test = from s in DtSample.AsEnumerable()
                           where s.Field<Int16>("Line_Number") == 5
                           select new
                           {
                               Sample_ID = s.Field<int>("Sample_ID"),
                               LineNumber = s.Field<Int16>("Line_number"),
                               count = (int)( from s2 in DtSample.AsEnumerable()
                                              where s.Field<int>("Status_ID") == s2.Field<int>("Status_ID") && 
                                              s.Field<Int16>("Line_Number") <= s2.Field<Int16>("Line_Number") && 
                                              s2.Field<Decimal?>("Weight") >= 1
                                              select s2).Count()
                  
                           };

    Tuesday, May 8, 2012 6:20 PM
  • Thank you to post your solution here.

    :)

    Have a nice day.


    Ghost,
    Call me ghost for short, Thanks
    To get the better answer, it should be a better question.

    Thursday, May 10, 2012 7:32 AM
  • //INITIALIZE THE QUERY Variable TO A DEFAULT VALUE
    var Query = from w in DtWorkSheet.AsEnumerable()
                select w;
    
    IF(true)
    {
    //OVERWRITE THE VALUE OF THE Variable Query
    Query = from w in DtWorkSheet.AsEnumerable()
         where w.Field<int>("LineNumber") == lngCurrLine
         select new 
         { 
          RackLine = (int)(from w2 [DaTable].AsEnumerable()
           where w2.Field<int>("val") == w.Field<int>("val") 
           && w2.Field<int>("val2") <= w.Field<int>("val2") 
           && w2.Field<int>("SampleWeight") >= minWeight 
       && SqlMethods.Like(w2.Field<string>("D"), "%" + strExcludeSampleText + "%")select w2).Count()
          };
    }
    
    
    //Get the value of Query 
    foreach( var q in Query)
    {
      MessageBox.Show(q.RackLine)
    }

    I repeated the code in the different (condition- IF)

    I also notice there was a "Like" statement which can be translate into LINQ using " SqlMethods.Like "  need to add a reference to your project.

    ** I wrote the code as exemple it has not been tested and I made some change to shorten the code this might now compile in the actual state. That might help someone a lot there is a lot of research into it. Not easy to find on google.

     
    • Marked as answer by Benoit Bolduc Thursday, May 10, 2012 7:32 PM
    Thursday, May 10, 2012 7:31 PM
  • Here my new challenge now  :

    http://social.msdn.microsoft.com/Forums/en/netfxbcl/thread/2eca04b7-9e90-4d94-8f27-a4444991e6bb

    I found not very easy to translate complexe TSQL to LINQ  am I missing something ?

    I'm new in LINQ and maybe that's not the best way to go.

    Thursday, May 10, 2012 8:05 PM