locked
Update without selecting the entity first? RRS feed

  • Question

  • Is is possible to prevent astoria from getting the entity out of the database before it updates it?

    I've got a heavy object (lots of properties, some blobs).  My client application just wants to update two properties.  I have figured out that if I create a new type that only has the 2 properties, like this:
        [global::System.Data.Services.Common.DataServiceKeyAttribute("WidgetId")]  
        public class SPATestClass  
        {  
            public Guid WidgetId { getset; }  
            public int WidgetAge { getset; }  
        }  
     

    I can pass it to the data context's update method and only those two properties will be passed through to the database. 

                SPATestClass test = new SPATestClass {   
                    WidgetId = widget.WidgetId,   
                    WidgetAge = widget.WidgetAge };  
                context.AttachTo("Widget", test);  
                context.UpdateObject(test);  
                context.SaveChanges();  
     

    Here is the resulting SQL:

    exec sp_executesql N'update [dbo].[Widget]  
    set [WidgetAge] = @0  
    where ([WidgetId] = @1)  
    ',N'@0 int,@1 uniqueidentifier',@0=999,@1='F4DD85CF-CE1E-E2D1-3171-650938ABD2B7' 

    Great!  However...before that, I find the following SQL in the Trace (It's called right before the update sql statement and its after the SaveChanges() method is called in the c# code):

    SELECT   
    [Extent1].[WidgetId] AS [WidgetId],   
    [Extent1].[WidgetName] AS [WidgetName],   
    [Extent1].[WidgetDescription] AS [WidgetDescription],   
    [Extent1].[WidgetStartDate] AS [WidgetStartDate],   
    [Extent1].[WidgetEndDate] AS [WidgetEndDate],   
    [Extent1].[WidgetAge] AS [WidgetAge],   
    [Extent1].[WidgetCost] AS [WidgetCost],   
    [Extent1].[WidgetPicture] AS [WidgetPicture],   
    [Extent1].[WidgetIcon] AS [WidgetIcon],   
    [Extent1].[WidgetFile] AS [WidgetFile],   
    [Extent1].[WidgetVersion] AS [WidgetVersion],   
    [Extent1].[WidgetCreated] AS [WidgetCreated],   
    [Extent1].[WidgetLastModified] AS [WidgetLastModified],   
    [Extent1].[WidgetParentId] AS [WidgetParentId],   
    [Extent1].[WidgetSort] AS [WidgetSort],   
    [Extent1].[WidgetPositionLastModified] AS [WidgetPositionLastModified],   
    [Extent1].[WidgetChildrenVersion] AS [WidgetChildrenVersion],   
    [Extent1].[WidgetChlldrenLastModified] AS [WidgetChlldrenLastModified],   
    [Extent1].[WidgetChildrenCount] AS [WidgetChildrenCount]  
    FROM [dbo].[Widget] AS [Extent1]  
    WHERE cast('f4dd85cf-ce1e-e2d1-3171-650938abd2b7' as uniqueidentifier) = [Extent1].[WidgetId] 

    I'm not sure if that is being passed up just to the server or all the way to the client.  Either way, it's noooooo good.  ;-)  WidgetFile might be 100MB.

    See my dilema?

    What can I do differently?

    Thanks much!
    Stew
    Saturday, February 14, 2009 5:43 AM

All replies

  • Hi Stewart,

    In V1 of Astoria , we dont have inbuilt support for Blobs . The behavior you are seeing is by-design in V1 .
    Even though the client model says that the entity contains only 2 properties , in response to a PUT/MERGE , the server sends back the entire changed entity
    so that the client can update its state .
    For now , I think splitting out the BLOB into its own entity can solve your dilemma.

    From this :

    class SPATestClass {
    public Guid WidgetID{get;set;}
    public int WidgetAge{get;set;}
    public byte[] WidgetContent{get;set;}



    Move to this :

    Server-side  entity
    class SPATestClass {
    public Guid WidgetID{get;set;}
    public int WidgetAge{get;set;}

    public Widget WidgetContent{get;set;}


    public class Widget {
    public byte[] WidgetData{get;set;}
    }

    On the Server-side Split the Widget field into its own entity .
    If you are using EF as your Provider on the Server-side , this article is useful :
    http://msdn.microsoft.com/en-us/library/bb738482.aspx

    Phani Raj Astoria
    Saturday, February 14, 2009 8:54 PM
    Moderator
  • Phani -

    Thanks for the response!!

    I will read the article you linked to and look into breaking it out. 

    Just so I understand, are you saying that the entire entity is returned to the client or is just returned from the database to the server side entity model? 

    On a similar note, I am working on a different situation that is having the same effect.  I have noticed that if I execute a query to get a single attribute of the widget above I get back the right response but there are two SQL statements executed against the database.  I am using the entity framework.

    This is the query:

    http://localhost/Protiviti.Test.WebApp/WidgetWebDataService.svc/Widgets(guid'f4dd85cf-ce1e-e2d1-3171-650938abd2b7')/WidgetChildrenVersion

    Here is the response:

      <?xml version="1.0" encoding="utf-8" standalone="yes" ?>   
      <WidgetChildrenVersion p1:type="Edm.Guid" 
        xmlns:
    p1="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" 
        xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices">
            313f80d4-f38e-4e34-9c0d-c32551910cac
        </WidgetChildrenVersion>  

    This is just what I would expect.  Alls good!

    However, when I check profiler I am showing 2 sql statements:

    SELECT   
    [Extent1].[WidgetId] AS [WidgetId],   
    [Extent1].[WidgetName] AS [WidgetName],   
    [Extent1].[WidgetDescription] AS [WidgetDescription],   
    [Extent1].[WidgetAge] AS [WidgetAge],   
    [Extent1].[WidgetCost] AS [WidgetCost],   
    [Extent1].[WidgetStartDate] AS [WidgetStartDate],   
    [Extent1].[WidgetEndDate] AS [WidgetEndDate],   
    [Extent1].[WidgetIcon] AS [WidgetIcon],   
    [Extent1].[WidgetPicture] AS [WidgetPicture],   
    [Extent1].[WidgetFile] AS [WidgetFile],   
    [Extent1].[WidgetVersion] AS [WidgetVersion],   
    [Extent1].[WidgetCreated] AS [WidgetCreated],   
    [Extent1].[WidgetLastModified] AS [WidgetLastModified],   
    [Extent1].[WidgetSort] AS [WidgetSort],   
    [Extent1].[WidgetParentId] AS [WidgetParentId],   
    [Extent1].[WidgetParentLastModified] AS [WidgetParentLastModified],   
    [Extent1].[WidgetChildrenCount] AS [WidgetChildrenCount],   
    [Extent1].[WidgetChildrenVersion] AS [WidgetChildrenVersion],   
    [Extent1].[WidgetChildrenLastModified] AS [WidgetChildrenLastModified]  
    FROM [dbo].[Widget] AS [Extent1]  
    WHERE cast('f4dd85cf-ce1e-e2d1-3171-650938abd2b7' as uniqueidentifier) = [Extent1].[WidgetId] 

    and

    SELECT   
    [Extent1].[WidgetChildrenVersion] AS [WidgetChildrenVersion]  
    FROM [dbo].[Widget] AS [Extent1]  
    WHERE cast('f4dd85cf-ce1e-e2d1-3171-650938abd2b7' as uniqueidentifier) = [Extent1].[WidgetId] 

    The second is what I would expect.  I didn't expect to see the first.  Is this related to the same issue with the lack of support for blob data?  Three of the properties WidgetFile, Icon, and Picture are blobs.

    Thanks!!
    Stew
    Sunday, February 15, 2009 8:50 AM
  • Phani -
    I looked into this a little more.  I tried what you mentioned and created different entities Widget and WidgetLite.  Each has its own table in the database.  Widget has the same properties (including blobs) as before.  WidgetLite only has an id name and description.  I tried a select single attribute and update for each.  In all operations the framework exectued two select statements.  The first selected the entire object and the second select or updated just the WidgetName attribute. 

    Is this expected?

    I have created a sample solution if you want to see what I am seeing.  

    http://cid-8490c2007f0b463b.skydrive.live.com/self.aspx/Public/AstoriaTestDataService.zip


    To run it:
    1. Deploy the database project to a local SQL 2008 database.
    2. Run the data generation plan in the database project.
    3. Run the solution (a wpf window will launch).
    4. Click each button and watch a sql trace against the database.

    Each button runs a single operation (select WidgetName for the WidgetId specified or update WidgetName for the WidgetId specified) and each operation results in two calls to the database (one selects the entire object and the second either selects the WidgetName or calls to update it).

    I can see why the update might need to make two calls.  I don't see why the select makes two calls.

    Any explanation will be greatly appreciated.  I'm just trying to figure out the best architecture in case the connection between my web server and database is not optimal.

    Thanks!!
    Stewart
    Sunday, February 15, 2009 8:48 PM

  •  Thanks for sending the project Stewart , I am looking into this now .
    Phani Raj Astoria
    Monday, February 23, 2009 5:39 PM
    Moderator
  • Phani -
    Thanks for taking a look at this.  Any luck in seeing what I saw?  Let me know if there is anything I can do.
    Thanks!
    Stewart
    Tuesday, February 24, 2009 7:56 PM