following WCF RIA Service Combining Two Tables and trying to add relationship tables with no luck

Question

Hello, I been following this article by Michael - http://lightswitchhelpwebsite.com/Blog/tabid/61/EntryId/47/WCF-RIA-Service-Combining-Two-Tables.aspx and

I don't see applicationdata.vb under \ServerGenerated\GeneratedArtifacts (in the LightSwitch project) for my project.   All I have in that folder is the following files: MyGroup.vb MyGroupData.vb Application.vb DataServiceImplementation.vb Since I didnt have applicationdata.vb, I used mygroupdata.vb but when I try to do the query code it just has a bunch of errors.  Also when I highlight my mouse over  "Private m_context As MyGroupDataObjectContext"  it says no metadata documentation available? am I somehow missing applicationData.vb therefore when trying to create the queries it can't find it.   If anyone can help? Here is my code in my domain service class if it helps. Option Compare Binary Option Infer On Option Strict On Option Explicit On Imports System Imports System.Collections.Generic Imports System.ComponentModel Imports System.ComponentModel.DataAnnotations Imports System.Linq Imports System.ServiceModel.DomainServices.Hosting Imports System.ServiceModel.DomainServices.Server Imports WCFRIA_AWG.MyGroupData.Implementation Imports System.Data.EntityClient Imports System.Web.Configuration 'TODO: Create methods containing your application logic. 'TODO: add the EnableClientAccessAttribute to this class to expose this DomainService to clients. Public Class DomainServiceAWG Inherits DomainService Private m_context As MyGroupDataObjectContext Public ReadOnly Property Context() As MyGroupDataObjectContext Get If Me.m_context Is Nothing Then Dim connString As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("_IntrinsicData").ConnectionString Dim builder As New EntityConnectionStringBuilder() builder.Metadata = "res://*/MyGroupData.csdl|res://*/MyGroupData.ssdl|res://*/MyGroupData.msl" builder.Provider = "System.Data.SqlClient" builder.ProviderConnectionString = connString Me.m_context = New MyGroupDataObjectContext(builder.ConnectionString) End If Return Me.m_context End Get End Property [Query(IsDefault = true)] public IQueryable<CombinedSources> GetAllSources() { var colArticles = from Articles in this.Context.Articles select new CombinedSources { // Note we turn the ID of the Internal Products to // A negative number so we don't have duplicates // with the External products ID = Articles.Id * (-1), Clients = Articles.Clients, Agents = Articles.Agents, Status = Articles.Status, EntryDate = Articles.EntryDate, Month = Articles.Month }; var colPressReleases = from PressReleases in this.Context.PressReleases select new CombinedSources { ID = PressReleases.Id, Clients = PressReleases.Clients, Agents = PressReleases.Agents, Status = PressReleases.Status, EntryDate = PressReleases.EntryDate, Month = PressReleases.Month }; return colArticles.Union(colPressReleases); } // Override the Count method in order for paging to work correctly protected override int Count<T>(IQueryable<T> query) { return query.Count(); } End Class Public Class CombinedSources <Key()> Public Property ID() As Integer Public Property Clients() As String Public Property Agents() As String Public Property Status() As String Public Property Entrydate() As Date Public Property Month() As Date End Class

Answer 1

Try following this Tutorial it's slightly different from Micheal's one but it achieves the same result

http://blogs.msdn.com/b/lightswitch/archive/2011/04/08/how-do-i-display-a-chart-built-on-aggregated-data-eric-erhardt.aspx

hope it helps!

---

Like Yann Said : "If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer". This will help people find the answers that they're looking for more quickly."

Answer 2

I got it to work for entities that are the same on both tables.  Now my issues is that these tables also have relationship columns that they share.  I get errors when I try to query an entity that is a relationship to ta table.   How can I make the WCF_RIA service query a table that has a relationship?

Answer 3

Can you show me the code you use and the tables structures?

---

Like Yann Said : "If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer". This will help people find the answers that they're looking for more quickly."

Answer 4

Hello NadJib, Below is my table, i have 2 more tables almost alike but they all share the same relationship tables of Clients, Authors, Networklocs and users.  So I want to show a dashboard or screen search from all 3 tables but some of the information i want to show is coming from the relationship tables.  I want to show the following columns on search screen

ID, entrydate, ClientName from Clients, User from Users, TargetMonth.  Below the screenshot is my Code, is the WCF_RIA service able to read from the relationship tables in order for me to show the data I want.

namespace WCF_AWG
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.ComponentModel.DataAnnotations;
    using System.Linq;
    using System.ServiceModel.DomainServices.Hosting;
    using System.ServiceModel.DomainServices.Server;
    using ApplicationData.Implementation;
    using System.Data.EntityClient;
    using System.Web.Configuration;

    public class CombinedWebSources
    {
        [MetadataType(typeof(Articles.Metadata))]

        public partial class Articles
        {
            internal sealed class Metadata
            {
                [Key()]
                public Int32 ID { get; set; }
            }
        }
        
        [MetadataType(typeof(Blogs.Metadata))]
        public partial class Blogs
        {
            internal sealed class Metadata
            {
                [Key()]
                public Int32 ID { get; set; }
            }
        }




    }


     // TODO: Create methods containing your application logic.
    // TODO: add the EnableClientAccessAttribute to this class to expose this DomainService to clients.
    public class WCF_AWG_Service : DomainService
    {
        #region Database connection
        private ApplicationDataObjectContext context;
        public ApplicationDataObjectContext Context
        {
            get
            {
                if (this.context == null)
                {
                    string connString =
                        System.Web.Configuration.WebConfigurationManager
                        .ConnectionStrings["_IntrinsicData"].ConnectionString;
                    EntityConnectionStringBuilder builder = new EntityConnectionStringBuilder();
                    builder.Metadata =
                        "res://*/ApplicationData.csdl|res://*/ApplicationData.ssdl|res://*/ApplicationData.msl";
                    builder.Provider =
                        "System.Data.SqlClient";
                    builder.ProviderConnectionString = connString;
                    this.context = new ApplicationDataObjectContext((builder.ConnectionString));
                }
                return this.context;
            }
        }
        #endregion


        [Query(IsDefault = true)]
        public IQueryable<CombinedWebSources> GetArticles()
        {
            return Context.Articles;
        }

        [Query(IsDefault = true)]
        public IQueryable<CombinedWebSources> GetBlogs()
        {
            return Context.Blogs;
        }

        // Override the Count method in order for paging to work correctly
        protected override int Count<T>(IQueryable<T> query)
        {
            return query.Count();
        }



    }
}

Thanks for your help.

Answer 5

Dbdmora,

Have you had a chance to look at Sheel's blog post on creating a RIA Service?

http://blogs.msdn.com/b/lightswitch/archive/2010/10/22/how-to-create-a-ria-service-wrapper-for-odata-source.aspx

This outlines how to add associations in your Ria Service.  Add an Association attribute to your Metadata class like so:

[MetadataType(typeof(Product.Metadata))]

       public partial class Product

       {

              internal sealed class Metadata

              {

                     [Key()]

                     public Int32 ID { get; set; }

                     [Association("Product_Category", "CategoryID", "ID", IsForeignKey = true)]

                     public Category Category { get; set; }

              }

       }

       [MetadataType(typeof(Category.Metadata))]

       public partial class Category

       {

              internal sealed class Metadata

              {

                     [Key()]

                     public Int32 ID { get; set; }

                     [Association("Product_Category", "ID", "CategoryID", IsForeignKey = false)]

                     public DataServiceCollection<Product> Products { get; set; }

              }

       }

Answer 6

Eric, when I try to add the code to from Sheel's blog to add the assoication relationships it breaks my code

I am not using an external DB, All my data is in lighswitch which why I believe I am having the issue understanding ths WCF_RIA concept to combine my tables with relationship tables when linking to applicationdata.cs

Here is my code that works as long as I don't add relationship entities. 

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.ComponentModel.DataAnnotations;
    using System.Linq;
    using System.ServiceModel.DomainServices.Hosting;
    using System.ServiceModel.DomainServices.Server;
    using ApplicationData.Implementation;
    using System.Data.EntityClient;
    using System.Web.Configuration;


namespace WCF_AWG
{
    public class CombinedWebSources
    {
        [Key]
        public int ID { get; set; }
        public DateTime EntryDate { get; set; }
        public string Clients { get; set; }
        public string SEMAgents { get; set; }
        public DateTime TargetMonth { get; set; }
        public string Status { get; set; }
    }

    // TODO: Create methods containing your application logic.
    // TODO: add the EnableClientAccessAttribute to this class to expose this DomainService to clients.
    public class WCF_AWG_Service : DomainService
    {
        #region Database connection
        private ApplicationDataObjectContext context;
        public ApplicationDataObjectContext Context
        {
            get
            {
                if (this.context == null)
                {
                    string connString =
                        System.Web.Configuration.WebConfigurationManager
                        .ConnectionStrings["_IntrinsicData"].ConnectionString;
                    EntityConnectionStringBuilder builder = new EntityConnectionStringBuilder();
                    builder.Metadata =
                        "res://*/ApplicationData.csdl|res://*/ApplicationData.ssdl|res://*/ApplicationData.msl";
                    builder.Provider =
                        "System.Data.SqlClient";
                    builder.ProviderConnectionString = connString;
                    this.context = new ApplicationDataObjectContext((builder.ConnectionString));
                }
                return this.context;
            }
        }
        #endregion


        [Query(IsDefault = true)]
        public IQueryable<CombinedWebSources> GetAllWebSources()
        {
            var colArticle = from Articles in this.Context.Articles
                             select new CombinedWebSources
                                      {
                                          // Note we turn the ID of the Internal Products to 
                                          // A negative number so we don't have duplicates
                                          // with the External products
                                          ID = Articles.Id * (-1),
                                          
                                          Status = Articles.Status
                                      };
            var colBlog = from Blog in this.Context.Blogs
                          select new CombinedWebSources
                                      {
                                          ID = Blog.Id,
                                      Status = Blog.Status
                                      };
            return colArticle.Union(colBlog);
        }
        // Override the Count method in order for paging to work correctly
        protected override int Count<T>(IQueryable<T> query)
        {
            return query.Count();
        }



    }
}

Below is what I added under the public Class CombinedWebSources. (continue below please) 

        public class CombinedWebSources
        {
            [Key]
            public int ID { get; set; }
            public DateTime EntryDate { get; set; }
            public string Clients { get; set; }
            public string SEMAgents { get; set; }
            public DateTime TargetMonth { get; set; }
            public string Status { get; set; }


            [MetadataType(typeof(Articles.Metadata))]

            public partial class Articles
            {
                internal sealed class Metadata
                {
                    [Key()]
                    public Int32 ID { get; set; }
                    [Association("Article_Clients", "ArticleID", "ID", IsForeignKey = true)]

                    public Clients Client { get; set; }

                }
            }
        }

This is the Query i added below tghe other queries

       [Query(IsDefault = true)]
        public IQueryable<CombinedWebSources> GetAllWebSources()
        {
            var colArticle = from Articles in this.Context.Articles
                             select new CombinedWebSources
                                      {
                                          // Note we turn the ID of the Internal Products to 
                                          // A negative number so we don't have duplicates
                                          // with the External products
                                          ID = Articles.Id * (-1),
                                          
                                          Status = Articles.Status
                                      };
            var colBlog = from Blog in this.Context.Blogs
                          select new CombinedWebSources
                                      {
                                          ID = Blog.Id,
                                      Status = Blog.Status
                                      };
            return colArticle.Union(colBlog);
        }


        [Query(IsDefault = true)]
        public IQueryable<ApplicationData.Implementation.Article> GetArticles()
        {
            return context.Articles;


        }

When I added this I get 498 warnings and It will not build. Here are just the first three warnings.
Warning 1 The type 'ApplicationData.Implementation.ApplicationDataObjectContext' in 'D:\Documents\Visual Studio 2010\Projects\AWG\AWG\ServerGenerated\GeneratedArtifacts\ApplicationData.cs' conflicts with the imported type 'ApplicationData.Implementation.ApplicationDataObjectContext' in 'd:\Documents\Visual Studio 2010\Projects\AWG\WCF_AWG\bin\Debug\WCF_AWG.dll'. Using the type defined in 'D:\Documents\Visual Studio 2010\Projects\AWG\AWG\ServerGenerated\GeneratedArtifacts\ApplicationData.cs'. D:\Documents\Visual Studio 2010\Projects\AWG\AWG\ServerGenerated\GeneratedArtifacts\DataServiceImplementation.cs 19 125 ServerGenerated

&

Warning 2 The type 'ApplicationData.Implementation.Authors' in 'D:\Documents\Visual Studio 2010\Projects\AWG\AWG\ServerGenerated\GeneratedArtifacts\ApplicationData.cs' conflicts with the imported type 'ApplicationData.Implementation.Authors' in 'd:\Documents\Visual Studio 2010\Projects\AWG\WCF_AWG\bin\Debug\WCF_AWG.dll'. Using the type defined in 'D:\Documents\Visual Studio 2010\Projects\AWG\AWG\ServerGenerated\GeneratedArtifacts\ApplicationData.cs'. D:\Documents\Visual Studio 2010\Projects\AWG\AWG\ServerGenerated\GeneratedArtifacts\ApplicationData.cs 21 179 ServerGenerated

&

Warning 3 The type 'ApplicationData.Implementation.Article' in 'D:\Documents\Visual Studio 2010\Projects\AWG\AWG\ServerGenerated\GeneratedArtifacts\ApplicationData.cs' conflicts with the imported type 'ApplicationData.Implementation.Article' in 'd:\Documents\Visual Studio 2010\Projects\AWG\WCF_AWG\bin\Debug\WCF_AWG.dll'. Using the type defined in 'D:\Documents\Visual Studio 2010\Projects\AWG\AWG\ServerGenerated\GeneratedArtifacts\ApplicationData.cs'. D:\Documents\Visual Studio 2010\Projects\AWG\AWG\ServerGenerated\GeneratedArtifacts\ApplicationData.cs 21 294 ServerGenerated

I don't know where the conflict is, Any suggestions on where I should put the code or what to modify in order for it to work to view my relationship date for each of my tables?  Thanks for any help in advance

Answer 7

Dbdmora,

From what I gather you have 3 tables:

• Blog
• Article
• Client

And you have successfully unioned Blog and Article into a "CombinedWebSource" table.

Now you want to add relationship information to CombinedWebSource.  I can think of 3 ways to do this:

1. Just add the needed display information to the CombinedWebSource class.  Here is the example (note I've trimmed irrelevant code):

    public class CombinedWebSources
    {
        [Key]
        public int ID { get; set; }
        public string ClientName { get; set; }
        public string Status { get; set; }
    }
    public class WCF_AWG_Service : DomainService
    {
        [Query(IsDefault = true)]
        public ObjectQuery<CombinedWebSources> GetAllWebSources()
        {
            var colArticle = from a in this.Context.Articles
                             select new CombinedWebSources
                             {
                                 // Note we turn the ID of the Internal Products to 
                                 // A negative number so we don't have duplicates
                                 // with the External products
                                 ID = a.Id * (-1),

                                 Status = a.Status,
                                 ClientName = a.Client.Name
                             };
            var colBlog = from b in this.Context.Blogs
                          select new CombinedWebSources
                          {
                              ID = b.Id,
                              Status = b.Status,
                              ClientName = b.Client.Name
                          };
            return colArticle.Union(colBlog);
        }

2. Create a Virtual Association between CombinedWebSources (from the WCF Ria Service) and Client table (from your ApplicationData data source).  To accomplish this, instead of having a "string ClientName" property on the CombinedWebSources class, have a "int ClientId" property.  The ClientId property will hold the identity of the client.  The query code for "GetAllWebSources()" will say "ClientId = b.Blog_Client" instead of "ClientName = b.Client.Name".

Update your WCF Ria Data Source by right-clicking on it and saying "Update Datasource" and click "Finish".

Now open the CombinedWebSources table in the designer by double-clicking on it.  At the top click the Add Relationship button.  On the right side select the "Client" table.  This makes a Many-to-One relationship from CombinedWebSource to Client.  However, you need to map how these tables relate to each other.  In the bottom grid, on the left side, open the first drop down and select "ClientId".  This says the CombinedWebSources ClientId property maps to the Client Id property.  Now you can use that relationship as you normally would in LightSwitch.

3. The way you are trying to do it is to also add a "Client" entity type to the WCF Ria Service.  This is a lot more complicated than the previous two solutions because now you will have duplicated "Client" types in your application, one in ApplicationData and one in your Ria service.  Because of this, I would recommend trying one of the first two approaches.  Adding a virtual association in LightSwitch is a lot simplier than trying to add an Association inside your Ria Service.