Solved

Filtering EntitySets

Posted on 2008-10-02
11
1,727 Views
Last Modified: 2013-12-17
I have a class called PageInfo which includes an EntitySet of PageContent.

When I write a LINQ query to return a PageInfo class based on the ID, I want to also return the PageContent for a specified language. At the moment, it returns PageContent for every language which is in the database. Is there anyway I can filter which PageContent is returned.

Thanks.
0
Comment
Question by:NIKKEN
  • 6
  • 5
11 Comments
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 22627563
Can you post the query and any relevant code please to see if we can help.
0
 

Author Comment

by:NIKKEN
ID: 22631843
The query that I currently have is attached. But I realised this just returns all the PageInfo entities where any of the PageContent entities fulfill pc.Culture == "en-GB".

What I want to do is return the PageInfo if the PageContent for en-GB exists, and if it does, only return the PageContent entity for en-GB, and not all the cultures.

Does this make sense?
var pageInfo =
                from page in db.GetTable<PageInfo>()
                where page.PageID == PageID
                     && page.PageContent.Any(pc => pc.Culture == "en-GB")
                select page;

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 22633769
Hi NIKKEN;

As I understand this statement, "What I want to do is return the PageInfo if the PageContent for en-GB exists, and if it does, only return the PageContent entity for en-GB, and not all the cultures.", then the following query should do it.

var pageInfo = from page in db.GetTable<PageInfo>()
                          where page.PageContent == "en-GB"
                         select page;

Fernando
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:NIKKEN
ID: 22634342
Hi Fernando.

Thanks but this will not work because PageContent is a EntitySet filled with PageContent classes.
Culture is just one of the variables inside the class.

I got what I wanted by using the following code but this is a terrible way of doing it because it will return loads more info from sql than I want it to return, then goes through and checks to see if the PageContent contains the correct Culture, and if it doesn't it just deletes the PageContent class.
var pageInfo =
                (from page in db.GetTable<PageInfo>()
                 where page.PageID == PageID
                      && page.PageContent.Any(pc => pc.Culture == Culture)
                 select page).SingleOrDefault();
 
for (int i = pageInfo.PageContent.Count - 1; i >= 0; i--)
{
     if (pageInfo.PageContent.ElementAt(i).Culture != Culture)
           pageInfo.PageContent.RemoveAt(i);
}

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 22636440
Can you post how the tables are created in the DB and the types of variable like Culture and any other variable?
0
 

Author Comment

by:NIKKEN
ID: 22666980
Here are the classes for both PageContent and PageInfo.

Hopefully it helps.
[Table(Name = "MicroSitesCMS_Pages")]
public class PageInfo
    {
        private int _pageID;
        [Column(IsPrimaryKey = true, Storage = "_pageID")]
        public int PageID
        {
            get { return _pageID; }
            set { _pageID = value; }
        }
 
        private int _siteID;
        [Column(Storage = "_siteID")]
        public int SiteID
        {
            get { return _siteID; }
            set { _siteID = value; }
        }
 
        private int? _parentPageID;
        [Column(Storage = "_parentPageID")]
        public int? ParentPageID
        {
            get { return _parentPageID; }
            set { _parentPageID = value; }
        }
 
        private int _statusID;
        [Column(Storage = "_statusID")]
        public int StatusID
        {
            get { return _statusID; }
            set { _statusID = value; }
        }
 
        private bool _enabled;
        [Column(Storage = "_enabled")]
        public bool Enabled
        {
            get { return _enabled; }
            set { _enabled = value; }
        }
 
        private int? _order;
        [Column(Storage = "_order")]
        public int? Order
        {
            get { return _order; }
            set { _order = value; }
        }
 
        private EntitySet<Status> _status;
        [Association(Name = "FK_MicroSitesCMS_Pages_MicroSitesCMS_Status", OtherKey = "StatusID", ThisKey = "StatusID", Storage = "_status")]
        public EntitySet<Status> Status
        {
            get { return _status; }
            set { _status = value; }
        }
 
        private EntitySet<PageContent> _pageContent;
        [Association(Name = "FK_MicroSitesCMS_PageContent_MicroSitesCMS_Pages", OtherKey = "PageID", ThisKey = "PageID", Storage = "_pageContent")]
        public EntitySet<PageContent> PageContent
        {
            get { return _pageContent; }
            set { _pageContent = value; }
        }
    }
 
[Table(Name = "MicroSitesCMS_PageContent")]
    public class PageContent
    {
        private int _pageContentID;
        [Column(IsPrimaryKey = true, Storage = "_pageContentID")]
        public int PageContentID
        {
            get { return _pageContentID; }
            set { _pageContentID = value; }
        }
 
        private int _pageID;
        [Column(Storage = "_pageID")]
        public int PageID
        {
            get { return _pageID; }
            set { _pageID = value; }
        }
 
        private string _content;
        [Column(Storage = "_content")]
        public string Content
        {
            get { return _content; }
            set { _content = value; }
        }
 
        private string _culture;
        [Column(Storage = "_culture")]
        public string Culture
        {
            get { return _culture; }
            set { _culture = value; }
        }
 
        private string _title;
        [Column(Storage = "_title")]
        public string Title
        {
            get { return _title; }
            set { _title = value; }
        }
 
        private string _description;
        [Column(Storage = "_description")]
        public string Description
        {
            get { return _description; }
            set { _description = value; }
        }
 
        private string _keywords;
        [Column(Storage = "_keywords")]
        public string Keywords
        {
            get { return _keywords; }
            set { _keywords = value; }
        }
 
        private string _menuText;
        [Column(Storage = "_menuText")]
        public string MenuText
        {
            get { return _menuText; }
            set { _menuText = value; }
        }
 
        private string _pageName;
        [Column(Storage = "_pageName")]
        public string PageName
        {
            get { return _pageName; }
            set { _pageName = value; }
        }
 
        private string _editedBy;
        [Column(Storage = "_editedBy")]
        public string EditedBy
        {
            get { return _editedBy; }
            set { _editedBy = value; }
        }
 
        private DateTime _date;
        [Column(Storage = "_date")]
        public DateTime Date
        {
            get { return _date; }
            set { _date = value; }
        }
 
        private string _createdBy;
        [Column(Storage = "_createdBy")]
        public string CreatedBy
        {
            get { return _createdBy; }
            set { _createdBy = value; }
        }
    }

Open in new window

0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 22669310
Hi NIKKEN;

Try it this way.

Fernando

var pageInfo =
                from page in db.GetTable<PageInfo>()
                where page.PageID == PageID
                from content in page.PageContent
                where content.Culture == Culture
                select content;
 
foreach( pInfo in pageInfo )
{
	Console.WriteLine("Page ID = {0} Site ID = {1} Culture = {2}", 
		pInfo.PageInfo.PageID, pInfo.PageInfo.SiteID, pInfo.Culture);
}

Open in new window

0
 

Author Comment

by:NIKKEN
ID: 22670400
This returns just a PageContent class not a PageInfo class.

I'm guessing because of the "select content", so I tried "select page" but  that returns a PageContent with multiple PageInfo's as before.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 22670886
This statement, "select content;" from the query should return the PageContent that has a content.Culture == Culture and in that content should have a reference to the PageInfo which is the parent of the PageContent. Therefore in the following statements:

foreach( pInfo in pageInfo )
{
        Console.WriteLine("Page ID = {0} Site ID = {1} Culture = {2}",
                pInfo.PageInfo.PageID, pInfo.PageInfo.SiteID, pInfo.Culture);
}

The parameters of the foreach are miss leading because they are PageContent that you are iterating through. They are the PageContent of the PageInfo that was selected in the first part of the query. Therefore as I showed in my last post to get to the values in the PageInfo you go through the PageContent. So for example when in the above foreach loop to get the SiteID of the PageInfo you would use pInfo.PageInfo.SiteID. Remember that pInfo is really a PageContent object.

0
 

Author Comment

by:NIKKEN
ID: 22676377
I see what you mean now.
Yes it did work, thanks very much for you help.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 22676924
Not a problem, glad I was able to help.  ;=)
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Web Form VB.Net  import CSV 4 36
Code works but it's slow 28 70
EF5: Question about Metadata Artifact Processing 4 15
.NET 2008 VB and C# 6 27
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question