none
How can i make linq query to SQL table RRS feed

  • Question

  • Hi!
    Please, Help me to solve such problem:
    I have an SQL table.
    To operate it, I used class LINQ to SQL and access to table by DataContext class.
    In my project I want to create a temporary table with the same structure, in witch i put some data.
    After filling temporary table  I want to write down in sql table only those data in temporary table  which are absent in SQL table.
    I write an example in VB2008, but it don't work.
    May be exist another way to create temporary table?

    Imports System.Linq
    Imports System.Data.Linq
    Public Class Form1
        Dim ds As New DataSet
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            
           'in this part I create LINQ to SQL table and this part of code work good
           
            Dim dc As New DataClasses1DataContext
            Dim Query1 = From c In dc.table1
            Console.WriteLine("SQL table")
            For Each item In Query1
                Console.WriteLine(item.kod)
            Next
           
    'in this part I create temporary table, fill data and create query it work too
            CreateDataset()
            Dim tb = ds.Tables("t1").AsEnumerable
            Dim Query2 = From d In tb
            Console.WriteLine("dataset table")
            For Each item In Query2
                Console.WriteLine(item("col"))
            Next
    'in this part I create query to 2 tables and it isn't work :-(
            Dim Query3 = From f In dc.table1 Join h In tb On f.kod Equals h("col") Select f.kod, f.id
            Console.WriteLine("result")
            For Each item In Query3
                Console.WriteLine(item.kod)
            Next
        End Sub
       
            Sub CreateDataset()
            Dim tb As DataTable = New DataTable("t1")
            tb.Columns.Add("col", Type.GetType("System.Int64"))
            ds.Tables.Add(tb)
            Dim r As DataRow
            r = tb.NewRow()
            r("col") = 1
            tb.Rows.Add(r)
            r = tb.NewRow()
            r("col") = 2
            tb.Rows.Add(r)
            r = tb.NewRow()
            r("col") = 3
            tb.Rows.Add(r)
        End Sub
    End Class

    Thursday, February 18, 2010 9:49 AM

Answers

  • Hi,

    Yes, this workaround is good.  However, the dc.Table1.AsEnumerable() will load all the data in the Table1 from the database and then make a JOIN query in memory via LINQ to Objects instead of LINQ to SQL.  

    It may cause performance issues if the Table1 contains lots of data.  I recommend you consider my workaround because it is a LINQ to SQL query and it will be translated into a SQL WHERE IN query.   You may use dc.Log = Console.Out to check the SQL statements generated by the LINQ to SQL provider. 


    Best Regards,
    Lingzhi Sun
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, February 24, 2010 12:39 PM
    Moderator
  • Hello,

    LINQ to SQL does not support to combine a database table and a local datatable into the JOIN query.  The only supported on the local collection method is .Contains(). 

    Please refer to the following workaround:
    ====================================================================
    Dim colList as List(Of Integer) = New List(Of Integer)()
    colList.Add(1)
    colList.Add(2)
    colList.Add(3)

    Dim query = From f In dc.Table1 Where colList.Contains(f.kod) Select f.kod, f.id
    ====================================================================


    Best Regards,
    Lingzhi Sun
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, February 24, 2010 9:19 AM
    Moderator
  • Thanks for your replay, Lingzhi Sun!

    I very long looked forward to hearing and for this time have found its

    :
    Dim Query = From f In dc.table1.AsEnumerable Join h In ds.Tables("t1").AsEnumerable On f.kod Equals h("col") Select f.kod, f.id
    • Marked as answer by 7965975 Wednesday, February 24, 2010 10:27 AM
    Wednesday, February 24, 2010 10:27 AM

All replies

  • Imports System.Linq
    Imports System.Data.Linq
    Public Class Form1
        Dim ds As New DataSet
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            
            CreateDataset()
            
            Dim dc As New DataClasses1DataContext
            Dim Query = From f In dc.table1 Join h In ds.Tables("t1").AsEnumerable On f.kod Equals h("col") Select f.kod, f.id
            For Each item In Query
                Console.WriteLine(item.kod)
            Next
        End Sub

        Sub CreateDataset()
            Dim tb As DataTable = New DataTable("t1")
            tb.Columns.Add("col", Type.GetType("System.Int64"))
            ds.Tables.Add(tb)
            Dim r As DataRow
            r = tb.NewRow()
            r("col") = 1
            tb.Rows.Add(r)
            r = tb.NewRow()
            r("col") = 2
            tb.Rows.Add(r)
            r = tb.NewRow()
            r("col") = 3
            tb.Rows.Add(r)
        End Sub

    try reducing the code to these lines and let me know if it works.

    regards,

    kashif
    Thursday, February 18, 2010 6:07 PM
  • Hi, kpbutt!
    This reducing code don't work.
    When I execute the program, on string: "For each item in Query"
    arise error "NotSupportedExeption".
     

    Friday, February 19, 2010 6:57 AM
  • Hello,

    LINQ to SQL does not support to combine a database table and a local datatable into the JOIN query.  The only supported on the local collection method is .Contains(). 

    Please refer to the following workaround:
    ====================================================================
    Dim colList as List(Of Integer) = New List(Of Integer)()
    colList.Add(1)
    colList.Add(2)
    colList.Add(3)

    Dim query = From f In dc.Table1 Where colList.Contains(f.kod) Select f.kod, f.id
    ====================================================================


    Best Regards,
    Lingzhi Sun
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, February 24, 2010 9:19 AM
    Moderator
  • Thanks for your replay, Lingzhi Sun!

    I very long looked forward to hearing and for this time have found its

    :
    Dim Query = From f In dc.table1.AsEnumerable Join h In ds.Tables("t1").AsEnumerable On f.kod Equals h("col") Select f.kod, f.id
    • Marked as answer by 7965975 Wednesday, February 24, 2010 10:27 AM
    Wednesday, February 24, 2010 10:27 AM
  • Hi,

    Yes, this workaround is good.  However, the dc.Table1.AsEnumerable() will load all the data in the Table1 from the database and then make a JOIN query in memory via LINQ to Objects instead of LINQ to SQL.  

    It may cause performance issues if the Table1 contains lots of data.  I recommend you consider my workaround because it is a LINQ to SQL query and it will be translated into a SQL WHERE IN query.   You may use dc.Log = Console.Out to check the SQL statements generated by the LINQ to SQL provider. 


    Best Regards,
    Lingzhi Sun
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, February 24, 2010 12:39 PM
    Moderator
  • Once again thanks for your remarks, Lingzhi Sun!
    I'll take in mind yor recommendaiton and use query with small data.
    I have chosen datatable that does not care of the size of the data placed in it.

    Best Regards,
    7965975

    Thursday, February 25, 2010 6:02 AM