Solved

Search 2 entities

Posted on 2013-01-10
5
106 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 74

Expert Comment

by:käµfm³d 👽
Comment Utility
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
Comment Utility
The type or namespace name 'Linq' does not exist in the namespace 'System.Data'

so sqlMethos is not working.
0
 
LVL 74

Expert Comment

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

Author Comment

by:Eamon
Comment Utility
Getting this error when I try to fill the list
0
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now