Databases
StackOverflow2013
Postsdbo
POlinq with Any and sql_variants
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POlinq with Any and sql_variants
    primarykey
    data
    text
    <p>i have a problem with sql_variants</p> <p>i have 2 tables and a DTO</p> <pre><code>'struct A_Table1 ' id (int)(not null) ' wert (sql_variant)(not null) 'struct A_Table2 ' id (int)(not null) ' wert (sql_variant)(not null) Private Class DTO Public Property id As Integer Public Property wert As Object End Class </code></pre> <p>I like to select everything from tbale 1 and if there is a value entered for the id of table 1 in table2 i like to get the value from there. This works great with strings (or at least one of the 'wert' columns beeing a string. But if both 'wert' columns are sql_variants, i do get nasty outputs: </p> <pre><code>System.Collections.Generic.List`1[System.Object] </code></pre> <p>as text inside the grid where i display the stuff.</p> <p>Here is some sample code</p> <pre><code>Private Sub Button2_Click_1(sender As System.Object, e As System.EventArgs) Handles Button2.Click Dim q = (From p In db.A_Table1s Select New DTO With { .id = p.id, .wert = If(db.A_Table2s.Any(Function(f) f.id = p.id And Not f.wert Is Nothing), (From v In db.A_Table2s Where v.id = p.id Select v.wert).FirstOrDefault, p.wert) }) bs3.DataSource = q grd.DataSource = bs3 End Sub And the generated SQL SELECT [t0].[id], (CASE WHEN EXISTS( SELECT NULL AS [EMPTY] FROM [dbo].[A_Table2] AS [t1] WHERE [t1].[id] = [t0].[id] ) THEN 1 ELSE 0 END) AS [value], [t0].[wert] FROM [dbo].[A_Table1] AS [t0] </code></pre> <p>That "AS [value]" looks "strange"</p> <p>Can someone tell me why this query won't work with sql_variants?</p> <p>thank you</p> <p>_rene</p> <p>Oh what i just realized: Linq is doing 2 additional querys (for every entry that is poresent in the second table:</p> <pre><code>SELECT TOP (1) [t0].[wert] FROM [dbo].[A_Table2] AS [t0] WHERE [t0].[id] = @x1 -- @x1: Input Int (Size = -1; Prec = 0; Scale = 0) [1] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1 SELECT TOP (1) [t0].[wert] FROM [dbo].[A_Table2] AS [t0] WHERE [t0].[id] = @x1 -- @x1: Input Int (Size = -1; Prec = 0; Scale = 0) [3] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1 </code></pre> <p>And it seems like both values are merged to the returning DTO as a list of Objects</p> <p>Edit: Ok i got this working but not with .Any but left outer join</p> <pre><code>Dim q = (From p In db.A_Table1s Group Join v In db.A_Table2s On p.id Equals v.id Into Group From v In Group.DefaultIfEmpty() Select New DTO With {.id = p.id, .wert = If(v.wert Is Nothing, p.wert, v.wert)}) </code></pre> <p>However it would be very great to know, why the .Any "solution" does not work</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USrene marxis
    1. USrene marxis
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POlinq with Any and sql_variants
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload