locked
Find One Item by Id RRS feed

  • Question

  • Hello,

    I have the 3 tables on a database:

    create table dbo.Boxes
    (
        Id int identity not null
            constraint Boxes_Id_PK primary key clustered (Id),
        Created datetime not null,
        Name nvarchar (80) not null
            constraint Boxes_Name_U unique
    );
    
    create table dbo.BoxesFiles
    (
      BoxId int not null,
      FileId int not null,
        constraint BoxesFiles_BoxId_FileId_PK primary key clustered (BoxId, FileId)
    );
    
    
    create table dbo.Files
    (
        Id int identity not null
            constraint Files_Id_PK primary key clustered (Id),
        Data varbinary (max) filestream null
            constraint Files_Data_DF default (0x),
        Flag nvarchar (40) null,
        [Key] uniqueidentifier rowguidcol not null
            constraint Files_Key_U unique
    
    ) filestream_on [STORAGE];
    
    
    

    How can I load the File with Id = 3 from Box with Id = 1 without loading the other files?

    I would like the query to be executed in the database and load just that file to not overload the memory.

    Thank You,

    Miguel

    Monday, September 19, 2011 4:11 PM

Answers

  • Hi Miguel.

    You can try the following query:

    var file = context.Files.Single(f => f.Id == 3 && f.Boxes.Any(b => b.Id == 1));
    Console.WriteLine(file.Id);
    
    

    It performs all query on Database side.

    The query suggested by Giorgi has some drawbacks. I ran it on my local Model, and it generated 2 sql requests to database that:

    1. select Box entity by Boxes.Id = 1;

    2. select all File entites that has BoxesFiles.BoxId = 1

    3. then (on client side), it select those that has File.Id = 2.

    I guess it happened due to my context had  LazyLoadingEnabled enabled.

     

    Regards

     

     

    • Marked as answer by MDMoura Tuesday, September 20, 2011 10:02 AM
    Tuesday, September 20, 2011 8:14 AM

All replies

  • Hi Miguel,

    probably you'd do this:

    var file = context.Boxes.Single(b => b.Id == 1).Files.Single(f => f.Id == 3);

     

    Thanks


    Clarity VS Precision
    Tuesday, September 20, 2011 6:49 AM
  • Hi Miguel.

    You can try the following query:

    var file = context.Files.Single(f => f.Id == 3 && f.Boxes.Any(b => b.Id == 1));
    Console.WriteLine(file.Id);
    
    

    It performs all query on Database side.

    The query suggested by Giorgi has some drawbacks. I ran it on my local Model, and it generated 2 sql requests to database that:

    1. select Box entity by Boxes.Id = 1;

    2. select all File entites that has BoxesFiles.BoxId = 1

    3. then (on client side), it select those that has File.Id = 2.

    I guess it happened due to my context had  LazyLoadingEnabled enabled.

     

    Regards

     

     

    • Marked as answer by MDMoura Tuesday, September 20, 2011 10:02 AM
    Tuesday, September 20, 2011 8:14 AM
  • Thank You,

    Miguel

    Tuesday, September 20, 2011 10:02 AM