Solved

Search 2 entities

Posted on 2013-01-10
5
117 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

738 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