none
Return all parent / child recs in a single Linq to SQL call RRS feed

  • Question

  • I am trying to return a list of services along with a list of service operations for each service in a single Linq to SQL call, but not quite sure how to do this.

    The result will be returned from a Web Service. In addition, the Service column should not appear in the child list as it is an FK and would be redundant.

    My Entities, I am thinking, should look something like this:

    [DataContract] [Table(Name = "SoaServiceStatus")]
    public class SoaServiceStatus
    {
      [DataMember] [Column(IsPrimaryKey = true)] 
      public string Service { get; set; }
    
      [DataMember] [Column] 
      public string Description { get; set; }
    
      [DataMember] [Column] 
      public bool OnlineStatus { get; set; }
    
      [DataMember] 
      public List<SoaServiceOperationStatus> Operations { get; set; }
    }
    
    [DataContract] [Table(Name = "SoaServiceOperationStatus")]
    public class SoaServiceOperationStatus
    {
      [Column(IsPrimaryKey = true)]
      public string Service { get; set; }
    
      [DataMember] [Column(IsPrimaryKey = true)]
      public string Operation { get; set; }
    
      [DataMember] [Column]
      public string Description { get; set; }
    
      [DataMember] [Column]
      public bool OnlineStatus { get; set; }
    }


    Friday, December 10, 2010 11:02 PM

Answers

  • Take a look at the dataloadoptions LoadWith method:

    using (SomeDataContext dc = new SomeDataContext())
    {
      DataLoadOptions dl = new DataLoadOptions();
      dl.LoadWith<SoaServiceStatus>(doc => doc.Operations);
      dc.LoadOptions = dl;

      var serviceStatuses = dc.SoaServiceStatus.ToList();
    }

     

    ...also, change the SoaServiceStatus.Operations member from List<T> to a EntitySet<T> and add the Association attribute so L2S knows what keys to use for the association.

     


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4
    • Marked as answer by Bill2010 Tuesday, December 14, 2010 4:04 PM
    Saturday, December 11, 2010 1:26 AM
    Answerer
  • Kris,

    I am close, but still unable to access the Operations list in Program.Main:

    [Table(Name = "SoaServiceOperationStatus")]
    public class SoaOperation
    {
      [Column(IsPrimaryKey = true)]
      public string Service { get; set; }
    
      [Column(IsPrimaryKey = true)]
      public string Operation { get; set; }
    
      [Column]
      public string Description { get; set; }
    
      [Column]
      public bool Available { get; set; }
    }
    

     

    [Table(Name = "SoaServiceStatus")]
    public class SoaService
    {
      [Column(IsPrimaryKey = true)]
      public string Service { get; set; }
    
      [Column]
      public string Description { get; set; }
    
      [Column]
      public bool Available { get; set; }
    
      [Association(OtherKey="Service")]
      public EntitySet<SoaOperation> Operations { get; set; }
    }
    

     

    public class SoaDB : DataContext
    {
      public Table<SoaService> SoaServices;
      public Table<SoaOperation> SoaOperations;
      public SoaDB(string conn) : base(conn) { }
    }
    

     

    public static List<SoaService> GetSoaServicesAndOperations()
    {
      var db = new SoaDB(@"Data Source=localhost;Initial Catalog=CareMC;User Id=test; Password=test");
      var dl = new DataLoadOptions();
    
      dl.LoadWith<SoaService>(s => s.Operations);
      db.LoadOptions = dl;
    
      var res = from s in db.SoaServices
        select s;
    
      return res.ToList<SoaService>();
    }
    

     

    static void Main(string[] args)
    {
      var ret = SoaRepository.GetSoaServicesAndOperations();
    
      foreach (SoaService svc in ret)
      {
        Console.WriteLine("Service: " + svc.Service + " Status:" + svc.Available.ToString());
    
        //foreach (var operation in svc.Operations) <==== Can't do this for some reason
        //{
    
        //}
      }
    }
    

    • Marked as answer by Bill2010 Tuesday, December 14, 2010 4:03 PM
    Monday, December 13, 2010 5:01 PM
  • I actually added those in in the app. The code above is from my test app. I was trying to do a foreach on the Operations list, but can't seem to make that work. I am only able to iterate over the Services, as seen in MAIN.
    • Marked as answer by Bill2010 Tuesday, December 14, 2010 4:03 PM
    Tuesday, December 14, 2010 1:59 AM
  • In that case, it should work. One thing you might want to try is to set the DeferredLoadingEnabled property to false before running the query. I'm not sure if deferred loading can interfer with DataLoadOptions, but it is possible...
     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4
    • Marked as answer by Bill2010 Tuesday, December 14, 2010 4:03 PM
    Tuesday, December 14, 2010 3:11 AM
    Answerer

All replies

  • Take a look at the dataloadoptions LoadWith method:

    using (SomeDataContext dc = new SomeDataContext())
    {
      DataLoadOptions dl = new DataLoadOptions();
      dl.LoadWith<SoaServiceStatus>(doc => doc.Operations);
      dc.LoadOptions = dl;

      var serviceStatuses = dc.SoaServiceStatus.ToList();
    }

     

    ...also, change the SoaServiceStatus.Operations member from List<T> to a EntitySet<T> and add the Association attribute so L2S knows what keys to use for the association.

     


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4
    • Marked as answer by Bill2010 Tuesday, December 14, 2010 4:04 PM
    Saturday, December 11, 2010 1:26 AM
    Answerer
  • Actually, I did a little poking and found a solution that seems elegant, but can't quite make it work. I am trying to return a list of WCF services and their status where each service, in turn, contains a list of supported operations and their status.

    So far I have the following, but unable to access the Operations Items for some reason:

      [Table(Name = "SoaServiceStatus")]
      public class SoaService
      {
        [Column(IsPrimaryKey = true)]
        public string Service { get; set; }
    
        [Column]
        public string Description { get; set; }
    
        [Column]
        public bool Available { get; set; }
    
        private EntitySet<SoaOperation> _Operations;
    
        [Association(Storage="_Operations", OtherKey="Service")]
        public EntitySet<SoaOperation> Operations 
        {
          get { return this._Operations; }
          set { this._Operations.Assign(value); }
        }
      }
    
      [Table(Name = "SoaServiceOperationStatus")]
      public class SoaOperation
      {
        [Column(IsPrimaryKey = true)]
        public string Service { get; set; }
    
        [Column(IsPrimaryKey = true)]
        public string Operation { get; set; }
    
        [Column]
        public string Description { get; set; }
    
        [Column]
        public bool Available { get; set; }
    
        private EntityRef<SoaService> _Service;
    
        [Association(Storage="_Service", ThisKey="Service")]
        public SoaService SoaService
        {
          get { return this._Service.Entity; }
          set { this._Service.Entity = value; }
        }
      }
    
      public class SoaDB : DataContext
      {
        public Table<SoaService> SoaServices;
        public Table<SoaOperation> SoaOperations;
        public SoaDB(string conn) : base(conn) { }
      }
    
      public static List<SoaService> GetSoaServicesAndOperations()
      {
        var db = new SoaDB(@"Data Source=localhost;Initial Catalog=CareMC;User Id=test; Password=test");
    
        var q =
          from s in db.SoaServices
          from o in s.Operations
          select s;
    
        return q.ToList<SoaService>()
      }
    
      static void Main(string[] args)
      {
        var ret = SoaRepository.GetSoaServicesAndOperations();
    
        foreach (SoaService svc in ret)
        {
          Console.WriteLine("Service: " + svc.Service + " Status:" + svc.Available.ToString());
            
          //foreach (svc.Operations ops in svc)
          //{
    
          //}
        }
      }
    

    Monday, December 13, 2010 3:53 PM
  • Kris,

    I am close, but still unable to access the Operations list in Program.Main:

    [Table(Name = "SoaServiceOperationStatus")]
    public class SoaOperation
    {
      [Column(IsPrimaryKey = true)]
      public string Service { get; set; }
    
      [Column(IsPrimaryKey = true)]
      public string Operation { get; set; }
    
      [Column]
      public string Description { get; set; }
    
      [Column]
      public bool Available { get; set; }
    }
    

     

    [Table(Name = "SoaServiceStatus")]
    public class SoaService
    {
      [Column(IsPrimaryKey = true)]
      public string Service { get; set; }
    
      [Column]
      public string Description { get; set; }
    
      [Column]
      public bool Available { get; set; }
    
      [Association(OtherKey="Service")]
      public EntitySet<SoaOperation> Operations { get; set; }
    }
    

     

    public class SoaDB : DataContext
    {
      public Table<SoaService> SoaServices;
      public Table<SoaOperation> SoaOperations;
      public SoaDB(string conn) : base(conn) { }
    }
    

     

    public static List<SoaService> GetSoaServicesAndOperations()
    {
      var db = new SoaDB(@"Data Source=localhost;Initial Catalog=CareMC;User Id=test; Password=test");
      var dl = new DataLoadOptions();
    
      dl.LoadWith<SoaService>(s => s.Operations);
      db.LoadOptions = dl;
    
      var res = from s in db.SoaServices
        select s;
    
      return res.ToList<SoaService>();
    }
    

     

    static void Main(string[] args)
    {
      var ret = SoaRepository.GetSoaServicesAndOperations();
    
      foreach (SoaService svc in ret)
      {
        Console.WriteLine("Service: " + svc.Service + " Status:" + svc.Available.ToString());
    
        //foreach (var operation in svc.Operations) <==== Can't do this for some reason
        //{
    
        //}
      }
    }
    

    • Marked as answer by Bill2010 Tuesday, December 14, 2010 4:03 PM
    Monday, December 13, 2010 5:01 PM
  • In the latest example, it looks like the DataContract and DataMember attributes are missing..? WCF needs those attributes to be able to serialize members and entities.
     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4
    Tuesday, December 14, 2010 1:36 AM
    Answerer
  • I actually added those in in the app. The code above is from my test app. I was trying to do a foreach on the Operations list, but can't seem to make that work. I am only able to iterate over the Services, as seen in MAIN.
    • Marked as answer by Bill2010 Tuesday, December 14, 2010 4:03 PM
    Tuesday, December 14, 2010 1:59 AM
  • In that case, it should work. One thing you might want to try is to set the DeferredLoadingEnabled property to false before running the query. I'm not sure if deferred loading can interfer with DataLoadOptions, but it is possible...
     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4
    • Marked as answer by Bill2010 Tuesday, December 14, 2010 4:03 PM
    Tuesday, December 14, 2010 3:11 AM
    Answerer
  • I found the problem. I needed to add a reference to System.Data.Linq in my test console project... all is good now.
    Tuesday, December 14, 2010 4:03 PM