Patrick’s Bytes

Just realize one problem with LINQ to SQL

Unable to serialize the session state. In ‘StateServer’ and ‘SQLServer’ mode, ASP.NET will serialize the session state objects, and as a result non-serializable objects or MarshalByRef objects are not permitted. The same restriction applies if similar serialization is done by the custom session state store in ‘Custom’ mode.

When you have your ASP.NET session state stored in SQL Server, when you put any instance of your LINQ to SQL object into the session, you will hit by the about stated error.

I am still working on a fix for this. Will post the solution here once I found it.

A lot of applications would like to return random results from database. For example an web ad engine that returns random ads from its database based on certain conditions.

To do this in LINQ to SQL you need a bit of programming in T-SQL because LINQ does not have a Random operator nor you can use System.Random in LINQ statements.

Fortunately, LINQ to SQL lets programmers expose their functions and stored procs as methods. So what we can do is create a view first:

CREATE VIEW RandomView
AS
SELECT NEWID() As ID

Then after that you create a function:

CREATE FUNCTION GetNewId
(
)
RETURNS uniqueidentifier
AS
BEGIN
RETURN (SELECT ID FROM RandomView)
END

In the LINQ to SQL Designer, drag and drop the function into the designer (pic below)

From your LINQ query you can then write something like this

var tools =
  from tool in db.Tools
  orderby db.GetNewId()
  select tool.Name;

Many thanks to Fabrice for his post here.

http://weblogs.asp.net/fmarguerie/archive/2008/01/10/randomizing-linq-to-sql-queries.aspx

Psst… if you looking to learn more about LINQ, ASP.NET 3.5, Silverlight 2 or SQL Server 2008, check out this website where you can download the books for free.

Free downloadable e-book offer- Introducing Microsoft Silverlight 2

Free download- “Introducing SQL Server 2008” e-book offer

During my ILL Lab tag team with Ervin about Linq to SQL, quite a number of attendees asked us about Linq to SQL performance compared to ADO.NET SQLConnection. I couldn’t provide too much information but I remember Rico Mariani’s blog on Linq performance so I asked them to search for it. Search no more, here is the direct link, 5 parts of them about detail study on Linq to SQL performance.

I made a PDF available here for yesterday’s post so that you can have a better view on the source codes.

Word documents can be tagged with elements from XML schema and also programmatically inject value into it. With OpenXML SDK and LINQ to XML, all these are even easier! Below are the steps.

Create a schema in Visual Studio

The follow details must be unique

1. targetNamespace

2. xmlns

3. xmlns:mstns

4. name of element and complexType

Open up Word 2007, click on Schema

Click on Add Schema

Select the schema you created just now in file dialog

Enter an alias, mostly try to follow the URI

Click OK on the next screen

XML Structure taskpane will be visible

Add the Document into the Word file by clicking on it, you will see the tags on the Word File

Add a table in between the Document tags as below, key in the field name also

Place cursor in the cell beside the Supplier cell, then click on Supplier at the XML Structure task pane.

You now can see the Supplier tags

Do the same for the rest of the elements

Between each tag put a space as a placeholder, Word at the backend will create a <w:t> element

You can now save and close the Word document

Open up Visual Studio

Now we want to write a program to inject value into the 4 elements defined in the document above, which are:

1. Supplier

2. Phone

3. Fax

4. AttentionTo

Create a console application, name it DocGenWithXmlSchema

Add reference to the libraries I want to use, they are DocumentFormat.OpenXml (OpenXML SDK),

All the main method of program.cs, define the XMLNamespace

If you didn’t add the Using statement, you can use the resolve context menu to do it. (C# only)

Create variable to hold the filename

Add a using block to open the Word document

Instantiate the MainDocumentPart and read it into XDocument

Then take out all the XElements of “w:customXml” which are the XML Schema Tag we put in the Word document. Use foreach to loop thru the elements

Insert a switch-case block within the foreach block

Since I know which values in the 4 tags I want to replace, I use case to find the tagname and replace the value

After the foreach block, save the document

Call it layered of n-tier also can, Serena Yeoh aka Firedancer who now works in Microsoft Consulting Services came out with a sample application based on the whitepaper - Application Architecture for .NET: Designing Applications and Services.

It is hosted at Codeplex and now at its 4th release. Besides demo how to built enterprise level application using .NET, it also features new cutting edge .NET 3.5 technologies such as LINQ, WPF, WCF and WF. By basing on the best practices of the MSDN Architecture whitepaper, this Expense Sample app also showcase and helps you to jumpstart SOA and S+S based architecture.

Again, here is the link

http://www.codeplex.com/LayerSample

In my previous post I discovered that Content Controls in WordprocessingML files which has data binding to a CustomXML part will not render properly. However, this ONLY apply if you programmatically replaces the CustomXML part but never modify the value of <w:t> within the <w:sdt> element just like in this Eric White’s video on YouTube or as per mentioned in the book [Pro SharePoint Solution Development] in Chapter 7 .

To make things clearer, lets look at the screen shots below. For a Word 2007 document with CustomXML data bound(AND also with the CustomXML modified programmatically), below is what it looks like by default when you open it with Office 2003 (or XP and 2000), the data does not appear (below).

Even though you see there is no problem when open it up with Office 2007 (below)

This is because the Compatibility Pack for Office 2007 File Format does not render the value data bound inside the <w:databinding> element but instead its take the value in <w:t> element, shown below:

<w:sdt>
- <w:sdtPr>
<w:dataBinding w:xpath=”/root[1]/name[1]” w:storeItemID=”{b6aa39be-c6d5-40ca-a66e-93dbd069104f}” />
  <w:id w:val=”3411243″ />
- <w:placeholder>
  <w:docPart w:val=”DefaultPlaceholder_22675703″ />
  </w:placeholder>
  <w:showingPlcHdr />
  <w:text />
  </w:sdtPr>
- <w:sdtContent>
- <w:p w:rsidR=”006D15FD” w:rsidRDefault=”00F43988″>
- <w:r w:rsidRPr=”00583873″>
- <w:rPr>
  <w:rStyle w:val=”PlaceholderText” />
  </w:rPr>
<w:t>Click here to enter text.</w:t>
  </w:r>
  </w:p>
  </w:sdtContent>
  </w:sdt>

So I created a generic project using the latest OpenXML SDK (April 08 CTP) and together with LINQ to XML to modify the content within <w:t> element with the value from the CustomXML part. You can download my full source code here, but basically this is how my solution works:

XNamespace w = @”http://schemas.openxmlformats.org/wordprocessingml/2006/main”;

        public void Convert(string fileName)
        {
            using (var wordDoc = WordprocessingDocument.Open(fileName, true))
            {
                var mainPart = wordDoc.MainDocumentPart;

                XmlReader reader;

                reader = XmlReader.Create(mainPart.GetStream(FileMode.Open, FileAccess.Read));

                XDocument mainXml = XDocument.Load(reader);

                string xpath;
                XElement t;
                var bindings = mainXml.Descendants(w + “dataBinding”);

This is where the magic works, grab XPath attribute value from all the <w:databinding> elements and then replace it into <w:t> element using GetValueFromCustomXmlParts method (details do refer my source code)

                foreach (XElement binding in bindings)
                {
                    xpath = binding.Attribute(w + “xpath”).Value.ToString();

                    t = binding.Parent.Parent.Descendants(w + “t”).First();
                    string textValue = GetValueFromCustomXmlParts(mainPart.CustomXmlParts, xpath, myns);

                    t.ReplaceNodes(textValue);

                }

                XmlDocument temp = new XmlDocument();
                temp.Load(mainXml.CreateReader());
                temp.Save(wordDoc.MainDocumentPart.GetStream(FileMode.Create, FileAccess.Write));
            }
        }

After that the Word 2007 document can be opened in Office 2003 and data are rendered successfully. This solution also works in other none-MS Office productivity suites such as ThinkOffice and WordPerfect.

Disclamer: This is just a quick fix or rather a proof of concept on how to solve the <w:databinding> element problem on a simple Word 2007 document, there are  many situations (or more complex document layout) I haven’t tested the solution on. Do download my solution at your own risk. If you bump into problems do let me know, but my help will only be on best effort basis.

By the way, here is Eric’s video on the new OpenXML SDK

[YouTube=http://www.youtube.com/watch?v=t_FYHd234ng]
YouTube - Open XML SDK demo and road map

LINQ to SQL greatly enhance our coding productivity but when it comes to serialization of the object (means using LINQ to SQL Object as data contract) for used in WCF as data contracts, its a headache as it does not really support binary or XML serialization out of the box.

The immediate solution is to create your own data contract and copy the data from your LINQ to SQL object to properties in the data contract. This approach is good as a quick fix but it is counter productive of retyping everything. On the mean time I think there us overhead of doing so as well.

So the solution is write a native helper class to take care of the serialization using reflection (I bet you will complain there is overhead using reflection as well!) but it up to you as I found somewhere in Code Project the solution to do this is published.

http://www.codeproject.com/KB/dotnet/linqsqlserialization.aspx