Solved

Search 2 entities

Posted on 2013-01-10
5
114 Views
Last Modified: 2013-01-17
I have 2 related entities.

public class Projects
    {
        [Key]
        public int ProjectID { get; set; }
        public int VersionNo { get; set; }        

        [StringLength(30)]
        public string CreateUserName { get; set; }


        public virtual ICollection<ProjectDetails> ProjectDetails { get; set; }

    } //public class Projects 

Open in new window

And
public class ProjectDetails
    { 
        public int ProjectDetailsID { get; set; }

        [Key, Column(Order = 1)]
        public int ProjectID { get; set; }
        [Key, Column(Order = 2)]
        public int VersionNo { get; set; }
              
        [StringLength(50), Display(Name = "Name")]
        public string ProjectLead { get; set; }



        public Projects Projects { get; set; } 
    
    } //public class ProjectDetails

Open in new window


I want to return the project and details to a gridview and am using this
public List<Projects> SearchProjectWithDetails(string SearchText, string sortExpression)
        {            

            using (var db = new CERTContext())
            {               
                List<Projects> PList = new List<Projects>();
                //PList = db.Projects.Where(p => p.ProjectID == 19).ToList();   //This worked
                                
                //This also works
                var Projects = (from p in db.Projects
                                where p.ProjectID > 10
                                orderby p.VersionNo descending
                                select p).Take(3);


                foreach (var Project in Projects)
                {
                    PList.Add(Project);
                }//foreach (var Function in Functions)

                return PList;

            }// using (var db = new CERTContext())

        }//public Projects GetProject(int ProjectId)

Open in new window


It does work but I would like to be able to search/filter on the Project lead.

Sql would have been select Projects.ProjectId , ProjectDetails.ProjectLead From
Projects inner join ProjectDetails on Projects.ProjectId = ProjectDetails.ProjectId
 and Projects.VersionNo= ProjectDetails.VersionNo
Where Projects .ProjectID > 10 And  ProjectDetails.ProjectLead  like '%Eamon%'
Order by Projects.VersionNo desc.

Can someone please help with the correct way to do this
Thanks
Eamon
0
Comment
Question by:Eamon
  • 3
  • 2
5 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38763306
Try:

using System.Data.Linq.SqlClient;

...

                var Projects = (from p in db.Projects
                                join d in db.ProjectDetails on p.VersionNo equals d.VersionNo
                                where p.ProjectID > 10 &&
                                      SqlMethods.Like(d.ProjectLead, "%Eamon%")
                                orderby p.VersionNo descending
                                select p).Take(3);

...

Open in new window

0
 
LVL 1

Author Comment

by:Eamon
ID: 38763410
The type or namespace name 'Linq' does not exist in the namespace 'System.Data'

so sqlMethos is not working.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38763812
Add a reference to System.Data.Linq.dll.
0
 
LVL 1

Author Comment

by:Eamon
ID: 38766278
Getting this error when I try to fill the list
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 38767114
I didn't realize this was L2E. Try using Contains instead:

....

                var Projects = (from p in db.Projects
                                join d in db.ProjectDetails on p.VersionNo equals d.VersionNo
                                where p.ProjectID > 10 &&
                                      d.ProjectLead.Contains("Eamon")
                                orderby p.VersionNo descending
                                select p).Take(3);

....

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

679 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