Need a simple direct SQL example

[cdikmen]

I am new to LINQ so please forgive my ignorance. Here is a SQL query that I need to replicate in LINQ:

Dim MyString as String = "101,304,444,986"

Dim vSQL as "SELECT FROM COMPANY WHERE comp_status = 'A' AND comp_id IN (" + MyString + ") ORDER BY comp_name"

I have tried the following direct SQL in LINQ but get weird results:

db.ExecuteQuery(Of crCompany)(vSQL.ToString)

I am not even sure how to "get to" the results from this statement (I need to loop through the result set), but it appears to return 468 rows when it should only return 4.

Any help would be greatly appreciated.

 

[jmcilhinney]

ExecuteQuery will return an IEnumerable(Of crCompany), so you would use a For Each loop to enumerate its items. Then you can obviously examine the contents of each one and try to determine what's gone wrong.

 

[jmcilhinney]

Also, given that you're using LINQ to SQL, I don't see why you're not making use of it. You don't need to execute SQL code at all. You should just do this:

Dim companyIDs As Integer() = {101, 304, 444, 986}

Dim companies = From c In db.crCompany _
                Where c.comp_status = "A" _
                AndAlso companyIDs.Contains(c.comp_id) _
                Order By c.comp_name _
                Select c

For Each company In companies.ToArray()
    MessageBox.Show(company.comp_name)
Next

There's not much point using LINQ to SQL if you're not going to use the LINQ part.