Solved

How do I add WHERE Clause to my entity query, to return a specific record?

Posted on 2014-11-03
21
240 Views
Last Modified: 2016-02-18
I am trying to select a specific record from the db using entity, but can't figure out how.

This is what is going on.  I'm trying to pass the record item_NumberID in to the query from a label control named lbQueryValue  then put the record values into variables.

 // This is the select query
           nlaCatalog ctlog = new nlaCatalog();

// this line doesn't work
          nlaCatalog ctlog = ctlog.ToList().Where(x => x.Item_NumberID == int.Parse(lbQueryValue.Text.ToString())).First();

            string itImage = ctlog.Item_Image.ToString();
             string itName = ctlog.Item_Name.ToString();
             string itDiscription = ctlog.Description.ToString();
             string itPrice = ctlog.Price.ToString();

Open in new window


One other thing:  because the table nlaCatalog does not have a relationship to any other table, it has no Navigation Properties.

Thanks
0
Comment
Question by:bmanmike39
  • 9
  • 7
  • 5
21 Comments
 
LVL 33

Expert Comment

by:it_saige
ID: 40420688
Your code looks fine but there may be a couple of things that you are not seeing (from a debugging or runtime standpoint).  First, int.Parse is going to throw an exception if the value cannot be parsed as an integer.  Second, returning the First record only returns if a record exists that matches the criteria.

Here is one thing I would do:
int id = -1;
if (int.TryParse(lbQueryValue.Text, out id))
{
	// This is the select query
	nlaCatalog ctlog = new nlaCatalog();

	// this line doesn't work
	nlaCatalog ctlog = ctlog.ToList().Where(x => x.Item_NumberID.Equals(id)).FirstOrDefault();

	string itImage = ctlog.Item_Image.ToString();
	string itName = ctlog.Item_Name.ToString();
	string itDiscription = ctlog.Description.ToString();
	string itPrice = ctlog.Price.ToString();
}
else
{
	// Log or display a message stating that the value in lbQueryValue is not an integer.
}

Open in new window


-saige-
0
 

Author Comment

by:bmanmike39
ID: 40420706
I get an red squiggly line under ctlog.ToList()

... the error message is: dose not contain a definition for 'ToList' and no extension method
0
 
LVL 33

Expert Comment

by:it_saige
ID: 40420712
Do you have a using System.Linq at the top of your file?  Can you provide the code for nlaCatalog?  If not, does nlaCatalog implement IEnumerable and IEnumerable<T>?

-saige-
0
 

Author Comment

by:bmanmike39
ID: 40420724
Yes i have using Linq.  but as i said, in my  .edmx  it show no Navigation Properties.  I have Nav Properties on some of the other tables with relationships  if this matters
0
 
LVL 33

Expert Comment

by:it_saige
ID: 40420733
Navigation properties should not be an issue here:

Navigation properties in the Entity Framework provide a way to navigate an association between two entity types.

It all depends on your implementation of nlaCatalog.  Does it carry a list of rows inside?  Does it implement IEnumerable<T> and IEnumerable.  What type does it inherit from?  That is what is really at issue here.

In order for you to use a linq statement, the class in question has to be either Enumerable/Queryable or the class contains a/many member(s) that is/are Enumerable/Queryable.

-saige-
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40420755
What is the variable that contains a instance of the DbContext?
Does the variable nlaCatalog map to the table in the database?
0
 

Author Comment

by:bmanmike39
ID: 40420763
When I'm typing it show all the rows in intellisense. regarding the IEnumerable<T>  I cant remember how to view and check that?
0
 
LVL 33

Expert Comment

by:it_saige
ID: 40420765
In the class definition for nlaCatalog, you would see something like:
public class nlaCatalog : IEnumerable<DataRow>

Open in new window


-saige-
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40420768
Hi bmanmike39;

You are working with Entity Framework and NOT DataSet,  DataTable objects correct?
0
 
LVL 33

Expert Comment

by:it_saige
ID: 40420769
I believe he is Fernando.  I'm just waiting to see what his class definition contains.  From his code example, I can only assume he believes that nlaCatalog is a datatable.

-saige-
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:bmanmike39
ID: 40420776
I see that i have errored in my context it's NLAMEMEntities2  The variable is int ctNumber

 
 NLAMEMEntities2 ctlog = new NLAMEMEntities2(); 
            
 int ctNumber = ctlog.ToList().Where(x => x.Item_NumberID.Equals(id)).FirstOrDefault();

Open in new window


Im still getting the same error.
0
 

Author Comment

by:bmanmike39
ID: 40420778
and searched for IEnumerable but can't find it in the app_code files
0
 
LVL 33

Expert Comment

by:it_saige
ID: 40420781
That is because unless NLAMEMEntities2 inherits from int, you are trying to cast the return from your linq statement, which would be an Enumerable<NLAMEMEntities2> into an integer value.

-saige-
0
 
LVL 33

Expert Comment

by:it_saige
ID: 40420783
What is the class definition for nlaCatalog?  If you provide that, we can probably provide better assistance rather than stabbing in the dark.

-saige-
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40420798
Hi bmanmike39;

Try it as follows.

// Assuming that the DbContext is as follows
NLAMEMEntities2 ctlog = new NLAMEMEntities2();  
// Assuming that nlaCatalog is the table in the database you wish to query, then try this.
nlaCatalog record = null;
int id = -1;
if (int.TryParse(lbQueryValue.Text, out id))
{
    // Note that nlaCatalogs may need to be adjusted depending on how pluralization is configured on your system
    record = ctlog.nlaCatalogs.Where(x => x.Item_NumberID == id).FirstOrDefault();
}

If the record variable is null the record was not found and if it has a non null value it is the record you are looking for, so test to make sure it has a value before using it.

Open in new window

0
 

Author Comment

by:bmanmike39
ID: 40420800
nlaCatalog is a table in the db, that i added through update model.

strangly the following code returns the data in a gridview.  sorry, I just a bit confused now.

 NLAMEMEntities2 ctlog = new NLAMEMEntities2();
 GridView1.DataSource = ctlog.nlaCatalogs.ToList();
        GridView1.DataBind();

Open in new window

0
 
LVL 33

Accepted Solution

by:
it_saige earned 400 total points
ID: 40420804
Ok, then what Fernando has suggested is your answer, as he stated though, I would ensure that you did indeed return a catalog from the database.  Putting it all together:
NLAMEMEntities2 ctlog = new NLAMEMEntities2();  
nlaCatalog record = null;
int id = -1;
if (int.TryParse(lbQueryValue.Text, out id))
{
	record = ctlog.nlaCatalogs.ToList().Where(x => x.Item_NumberID == id).FirstOrDefault();
	if (record != null)
	{
		string itImage = record.Item_Image.ToString();
		string itName = record.Item_Name.ToString();
		string itDiscription = record.Description.ToString();
		string itPrice = record.Price.ToString();
	}
	else
	{
		// Log or display a message stating that the catalog was not found in the database.
	}
}
else
{
	// Log or display a message stating that the lbQueryValue was not an integer.
}

Open in new window


-saige-
0
 
LVL 62

Assisted Solution

by:Fernando Soto
Fernando Soto earned 100 total points
ID: 40420810
// This line of code instantiate the DbContext.
NLAMEMEntities2 ctlog = new NLAMEMEntities2();
// This line of code returns all the records in the table in the database in a List of nlaCatalog
GridView1.DataSource = ctlog.nlaCatalogs.ToList();
// This line of code binds the data to the web page
GridView1.DataBind();

Open in new window


The code in my last post should work then.
0
 

Author Closing Comment

by:bmanmike39
ID: 40420870
Thanks,  Excellent!
0
 
LVL 33

Expert Comment

by:it_saige
ID: 40420883
@bman, while I appreciate the points, Fernando came up with your solution.  I feel he should get the bulk, if not all, of the points for this one.

Should accept as answer: http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_28549962.html#a40420798

-saige-
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40421482
Thanks -saige- for your recognition on this question but I believe that it would be fair to award the point as 50 / 50 seeming we both contributed to solving this question.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

912 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