none
Does Linq-to-sql work with advanced SQL Server 2008 R2 features like filestream? RRS feed

  • Question

  • Problem

    AdventureWorks2008R2 HumanResources.Employee table cannot be added to designer surface in Visual Studio 2010 SP1. Nor can I add the Production.Document table.

    My environment

    I am using Visual Studio 2010 Ultimate, with Visual Studio 2010 SP1 from March 2011 installed, running on Windows 7. I am talking to a SQL2008 R2 server (version 10.50.1617), installed on Windows Server 2008 R2. All of these are running in VMs on Hyper-V.

    Details

    I did a simple experiment. I installed the AdventureWorks2008R2 database, created a console application, and intended to start coding.

    First, I created a Link-to-sql item for the database AdventureWorks2008R2 . I tried to add the HumanResources.Employee table to the designer surface by using the standard technique of dragging from server explorer. But it complained that "One or more selected items contain a data type that is not supported by the designer". My searches on the net did not reveal anything helpful.

    It is happy when I drag the HumanResources.Department table to the designer surface. What is wrong here?

    I tried other approaches:

    Then, I created an Entity Framework Model. I used the Generate from Database option and tried to add HumanResources.Employee to the model. It complains that "Unable to generate the model because of the following exception: 'The table 'AdventureWorks2008R2.Production.Document' is referenced by a relationship, but cannot be found."

    Then I examine and discover that Employee has a relationship to Production.Document. It uses FileStream stuff in case that matters.

    I even tried retargeting the console application to use the June 2011 Entity Framework CTP in case they had made improvmeents. Same results.

    Are these well-known limitations of some of the new SQL 2008 R2 features? If not, what am I doing wrong? If so, what technology should I be using to access SQL 2008 R2 features? And where are these limitations spelled out?

    thanks


    Best Regards, David K Allen http://codecontracts.info Minneapolis, Minnesota, USA
    Monday, July 18, 2011 7:40 PM

All replies

  • Hi David,

    Welcome!

    Both of Linq to SQL and EF support FileStream, you can refer the  the two links are help: http://blogs.microsoft.co.il/blogs/bursteg/archive/2008/05/12/linq-to-sql-filestream.aspx

    http://blogs.microsoft.co.il/blogs/bursteg/archive/2008/05/12/ado-net-entity-framework-support-filestream.aspx

    By the way, I test the AdventureWorks2008R2, it works on my computer and I didn't find any filestream fields.

    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, July 19, 2011 8:22 AM
    Moderator
  • I'm new to Linq so I don't know if my answer will be helpfull....

    I believe you have to turn on indexing on SQL Server and have the indexes dynamically built before you can use the designer...

    Tuesday, July 19, 2011 2:32 PM
  • When you say "it works on my computer" do you mean you were successful in dragging and dropping the HumanResources.Employee table on to the designer surface without errors being displayed?

     

     


    Best Regards, David K Allen http://codecontracts.info Minneapolis, Minnesota, USA
    Tuesday, July 19, 2011 11:28 PM
  • When you say "I didn't find any filestream fields." perhaps you were looking at the object expolorer or the design view. Indeed, those views do not reveal the FILE_STREAM connection.
    However, if I generate the sql script that defines the Production.Document table, I get DDL below. Notice there is one field that uses FILESTREAM technology, and a couple of other clauses I do not fully understand.

    [Document] [varbinary](max) FILESTREAM NULL,
    
    

    That is what I meant by "filestream stuff." I don't understand filestream stuff. But I recognize it when I see it. The AdventureWorks2008R2 database contains a FileGroup named "Document2008R2FileStreamGroup".  And seems reasonable to guess that these are what cause Linq to SQL problems. I am just trying to get someone to point me to some authoritative literature on the topic or at least get this confirmed by someone who knows this stuff.

    CREATE TABLE [Production].[Document](
    	[DocumentNode] [hierarchyid] NOT NULL,
    	[DocumentLevel] AS ([DocumentNode].[GetLevel]()),
    	[Title] [nvarchar](50) NOT NULL,
    	[Owner] [int] NOT NULL,
    	[FolderFlag] [bit] NOT NULL,
    	[FileName] [nvarchar](400) NOT NULL,
    	[FileExtension] [nvarchar](8) NOT NULL,
    	[Revision] [nchar](5) NOT NULL,
    	[ChangeNumber] [int] NOT NULL,
    	[Status] [tinyint] NOT NULL,
    	[DocumentSummary] [nvarchar](max) NULL,
    	[Document] [varbinary](max) FILESTREAM NULL,
    	[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
    	[ModifiedDate] [datetime] NOT NULL,
     CONSTRAINT [PK_Document_DocumentNode] PRIMARY KEY CLUSTERED 
    (
    	[DocumentNode] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [Document2008R2FileStreamGroup],
    UNIQUE NONCLUSTERED 
    (
    	[rowguid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] FILESTREAM_ON [Document2008R2FileStreamGroup]
    
    

     


    Best Regards, David K Allen http://codecontracts.info Minneapolis, Minnesota, USA
    Wednesday, July 20, 2011 1:06 AM
  • Hi David,

    Thanks for your code.

    The T-SQL is different with mine. I mayn't install a right version of AdventureWorks. I want to know whether the links which I provided in the first post works for you?

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, July 21, 2011 9:30 AM
    Moderator
  • Thanks for the suggestion.

    I did read the posts and I performed the exercises he outlines. However, I performed them in Visual Studio 2010. I had no trouble creating the datbase and table with the column associated with a Filestream. And I had no trouble creating a linq-to-sql model on this database. I could easily drag and drop the table File to my Linq2sql desinger surface.  So, yes, we confirmed that FileStream technology can work with linq2sql in some situations. And we confirmed that filestream technology works on my particular sql server 2008 R2 installation. And we confirmed that linq2sql can work on my visual studio 2010 installation with this particular simple schema he provided.

    However, that was not my question. I am not interested in the many cases where it works. I am interested in a particular case where it does NOT work.  My original question was about the behavior of Visual Studio 2010 with the AdventureWorks2008R2 database with a particular few tables as described in the original post. I am trying to determine what it is about the Employee or Document tables that make the linq2sql or EF designers incapable of handling them.

     

     


    Best Regards, David K Allen http://codecontracts.info Minneapolis, Minnesota, USA
    Thursday, July 21, 2011 12:03 PM
  • Here is where the AdventureWorks2008R2 database is posted, in case that is helpful to you. It is newer than AdventureWorks2008 database and has some of the new features in it.

    http://msftdbprodsamples.codeplex.com/wikipage?title=AW2008Details


    Best Regards, David K Allen http://codecontracts.info Minneapolis, Minnesota, USA
    Thursday, July 21, 2011 12:05 PM
  • Hi David,

    Thanks, I will try it!

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, July 26, 2011 10:09 AM
    Moderator
  • Did you ever sort it out, David. I ran into the same wall here. vs 2010 32-bit with all updates.
    Tuesday, January 17, 2012 1:43 PM
  • No, rothschild86. I'm sorry. I never did get it to work so I let it go.
    Best Regards, David K Allen http://codecontracts.info Minneapolis, Minnesota, USA
    Tuesday, January 17, 2012 3:06 PM