none
Problems with Designer RRS feed

  • Question

  • Hi,

    I just set up a small DB-structure with relations and created a Linq2SQL class for it through the VS Linq2SQL designer.

    Unforunately, there's a problem:

    Look at the diagramm, the designer produced: http://cid-fe2ce7647672b828.office.live.com/self.aspx/.Public/database.png

    So for example, since Song and Album have a realtionship, there's not only a AlbumID-Property in the Song-Class but also a Album-Property pointing directly to Album connected with the Song.

    And as you can see, Song (and Tag) also both have a relationship with TagMap, they all have the proper id-properties, but neither Song nor Tag have a TagMaps-Property and TagMap doesn't have a Song or a Tag-Property.

    Is anyone able to explain me why and how I can add this?

    The relations are set up properly, the SQL Server Management Studio shows me the same diagramm like the Linq2SQL-Designer with all relations (http://cid-fe2ce7647672b828.office.live.com/self.aspx/.Public/database2.png)

     

    Tobi

    Saturday, September 11, 2010 1:29 AM

Answers

  • Tagmaps don't have a primary key in the db according to the db diagram pic you posted. Give it a primary key and re-map it and it should work.
     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Proposed as answer by KristoferAEditor Saturday, September 11, 2010 4:57 AM
    • Marked as answer by liurong luo Tuesday, September 14, 2010 3:18 AM
    Saturday, September 11, 2010 4:57 AM
    Answerer
  • Wow, thanks, that was the problem.

    And I just got another 2 :D

    1. When I used SQL CE with Linq2SQL, there was a feature activated which automatically differenciated between plural and singulare.

    (like my table is called Albums, so the Linq-class for the content of 'Albums' is called Album and if another class has a Property which returns all associated Albums has been called 'Albums' again)

    Now I use SQL Express and this is not happening at all, either everything is called Albums or Album, no differenciation. Is there any way to turn on this behavior again?

    2. I found a sample for a dynamic sql query and customized it to fit my needs:

     

    There is a setting under Tools/Options/Database Tools/OR Designer that control if name pluralization is enabled or not. It is on by default in the English version of Visual Studio, and off by default in localized versions of VS.

    Alternatively, if you want more detailed control over pluralization/singularization and other aspects of class and property names (e.g. add/remove prefixes/suffixes, regex naming, remove underscores, plug in custom code for non-english pluralization/singularization etc), take a look at my add-in: http://www.huagati.com/dbmltools/

     

    ALTER PROCEDURE [dbo].[GetSongs] 
    
    @StartIndex int, 
    
    @EndIndex int, 
    
    @OrderBy varchar(500) 
    
    AS 
    
    BEGIN 
    
    SET NOCOUNT ON; 
    
    DECLARE @SQLStatement varchar(1000) 
    
    SET @SQLStatement = 
    
    'SELECT * ' + 
    
    'FROM Songs song1 ' + 
    
    'JOIN ' + 
    
    '(SELECT ID AS ID2, ' + 
    
    'ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') AS ''RowNumber'' ' + 
    
    'FROM Songs)' + 
    
    ' song2 ' + 
    
    'ON song1.ID = song2.ID2 ' + 
    
    'WHERE song2.RowNumber BETWEEN ' + CAST(@StartIndex as varchar(10)) + ' AND ' + CAST(@EndIndex as varchar(10)) 
    
    PRINT @SQLStatement 
    
    EXEC(@SQLStatement) 
    
    END
    
    
    
    

     

    When I execute it for a test in the Stored procedures' designer, I get this as output:

     

    Ausführen von [dbo].[GetSongs] ( @StartIndex = 0, @EndIndex = 1, @OrderBy = Title ).
    
    
    
    SELECT * FROM Songs song1 JOIN (SELECT ID AS ID2, ROW_NUMBER() OVER (ORDER BY Title) AS 'RowNumber' FROM Songs) song2 ON song1.ID = song2.ID2 WHERE song2.RowNumber BETWEEN 0 AND 1
    
    
    
    
    
    [--Data--](Replaced because too large)                                      
    
    Keine Zeilen betroffen.
    
    (1 Zeile(n) zurückgegeben)
    
    @RETURN_VALUE = 0
    
    Ausführen von [dbo].[GetSongs] beendet.
    
    

     

    My problem is, that an int is returned and I wondering why (since the dynamic function is a select and not a count function).

     

    Tobi


    L2S uses 'set fmtonly on' when running stored procs to determine their return type without actually running queries and statements. One side-effect of that is that stored procs using dynamic SQL will incorrectly be mapped as returning an int.

    You can work around this by adding 'set fmtonly off' in the beginning of such stored procs, in order to allow L2S to execute the stored proc and run it when mapping. You may need to supply default values to required params (if any) in the stored proc signature to avoid exceptions when doing the mapping.


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Proposed as answer by KristoferAEditor Monday, September 13, 2010 2:51 AM
    • Marked as answer by liurong luo Tuesday, September 14, 2010 3:18 AM
    Monday, September 13, 2010 2:51 AM
    Answerer

All replies

  • Tagmaps don't have a primary key in the db according to the db diagram pic you posted. Give it a primary key and re-map it and it should work.
     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Proposed as answer by KristoferAEditor Saturday, September 11, 2010 4:57 AM
    • Marked as answer by liurong luo Tuesday, September 14, 2010 3:18 AM
    Saturday, September 11, 2010 4:57 AM
    Answerer
  • Wow, thanks, that was the problem.

    And I just got another 2 :D

    1. When I used SQL CE with Linq2SQL, there was a feature activated which automatically differenciated between plural and singulare.

    (like my table is called Albums, so the Linq-class for the content of 'Albums' is called Album and if another class has a Property which returns all associated Albums has been called 'Albums' again)

    Now I use SQL Express and this is not happening at all, either everything is called Albums or Album, no differenciation. Is there any way to turn on this behavior again?

    2. I found a sample for a dynamic sql query and customized it to fit my needs:

    ALTER PROCEDURE [dbo].[GetSongs] 
    @StartIndex int, 
    @EndIndex int, 
    @OrderBy varchar(500) 
    AS 
    BEGIN 
    SET NOCOUNT ON; 
    DECLARE @SQLStatement varchar(1000) 
    SET @SQLStatement = 
    'SELECT * ' + 
    'FROM Songs song1 ' + 
    'JOIN ' + 
    '(SELECT ID AS ID2, ' + 
    'ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') AS ''RowNumber'' ' + 
    'FROM Songs)' + 
    ' song2 ' + 
    'ON song1.ID = song2.ID2 ' + 
    'WHERE song2.RowNumber BETWEEN ' + CAST(@StartIndex as varchar(10)) + ' AND ' + CAST(@EndIndex as varchar(10)) 
    PRINT @SQLStatement 
    EXEC(@SQLStatement) 
    END
    
    

    When I execute it for a test in the Stored procedures' designer, I get this as output:

    Ausführen von [dbo].[GetSongs] ( @StartIndex = 0, @EndIndex = 1, @OrderBy = Title ).
    
    SELECT * FROM Songs song1 JOIN (SELECT ID AS ID2, ROW_NUMBER() OVER (ORDER BY Title) AS 'RowNumber' FROM Songs) song2 ON song1.ID = song2.ID2 WHERE song2.RowNumber BETWEEN 0 AND 1
    
    
    [--Data--](Replaced because too large)                                                                            
    Keine Zeilen betroffen.
    (1 Zeile(n) zurückgegeben)
    @RETURN_VALUE = 0
    Ausführen von [dbo].[GetSongs] beendet.
    

    My problem is, that an int is returned and I wondering why (since the dynamic function is a select and not a count function).

     

    Tobi

    Saturday, September 11, 2010 9:37 AM
  • Wow, thanks, that was the problem.

    And I just got another 2 :D

    1. When I used SQL CE with Linq2SQL, there was a feature activated which automatically differenciated between plural and singulare.

    (like my table is called Albums, so the Linq-class for the content of 'Albums' is called Album and if another class has a Property which returns all associated Albums has been called 'Albums' again)

    Now I use SQL Express and this is not happening at all, either everything is called Albums or Album, no differenciation. Is there any way to turn on this behavior again?

    2. I found a sample for a dynamic sql query and customized it to fit my needs:

     

    There is a setting under Tools/Options/Database Tools/OR Designer that control if name pluralization is enabled or not. It is on by default in the English version of Visual Studio, and off by default in localized versions of VS.

    Alternatively, if you want more detailed control over pluralization/singularization and other aspects of class and property names (e.g. add/remove prefixes/suffixes, regex naming, remove underscores, plug in custom code for non-english pluralization/singularization etc), take a look at my add-in: http://www.huagati.com/dbmltools/

     

    ALTER PROCEDURE [dbo].[GetSongs] 
    
    @StartIndex int, 
    
    @EndIndex int, 
    
    @OrderBy varchar(500) 
    
    AS 
    
    BEGIN 
    
    SET NOCOUNT ON; 
    
    DECLARE @SQLStatement varchar(1000) 
    
    SET @SQLStatement = 
    
    'SELECT * ' + 
    
    'FROM Songs song1 ' + 
    
    'JOIN ' + 
    
    '(SELECT ID AS ID2, ' + 
    
    'ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') AS ''RowNumber'' ' + 
    
    'FROM Songs)' + 
    
    ' song2 ' + 
    
    'ON song1.ID = song2.ID2 ' + 
    
    'WHERE song2.RowNumber BETWEEN ' + CAST(@StartIndex as varchar(10)) + ' AND ' + CAST(@EndIndex as varchar(10)) 
    
    PRINT @SQLStatement 
    
    EXEC(@SQLStatement) 
    
    END
    
    
    
    

     

    When I execute it for a test in the Stored procedures' designer, I get this as output:

     

    Ausführen von [dbo].[GetSongs] ( @StartIndex = 0, @EndIndex = 1, @OrderBy = Title ).
    
    
    
    SELECT * FROM Songs song1 JOIN (SELECT ID AS ID2, ROW_NUMBER() OVER (ORDER BY Title) AS 'RowNumber' FROM Songs) song2 ON song1.ID = song2.ID2 WHERE song2.RowNumber BETWEEN 0 AND 1
    
    
    
    
    
    [--Data--](Replaced because too large)                                      
    
    Keine Zeilen betroffen.
    
    (1 Zeile(n) zurückgegeben)
    
    @RETURN_VALUE = 0
    
    Ausführen von [dbo].[GetSongs] beendet.
    
    

     

    My problem is, that an int is returned and I wondering why (since the dynamic function is a select and not a count function).

     

    Tobi


    L2S uses 'set fmtonly on' when running stored procs to determine their return type without actually running queries and statements. One side-effect of that is that stored procs using dynamic SQL will incorrectly be mapped as returning an int.

    You can work around this by adding 'set fmtonly off' in the beginning of such stored procs, in order to allow L2S to execute the stored proc and run it when mapping. You may need to supply default values to required params (if any) in the stored proc signature to avoid exceptions when doing the mapping.


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Proposed as answer by KristoferAEditor Monday, September 13, 2010 2:51 AM
    • Marked as answer by liurong luo Tuesday, September 14, 2010 3:18 AM
    Monday, September 13, 2010 2:51 AM
    Answerer