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

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
bmanmike39Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

it_saigeDeveloperCommented:
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-
bmanmike39Author Commented:
I get an red squiggly line under ctlog.ToList()

... the error message is: dose not contain a definition for 'ToList' and no extension method
it_saigeDeveloperCommented:
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-
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

bmanmike39Author Commented:
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
it_saigeDeveloperCommented:
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-
Fernando SotoRetiredCommented:
What is the variable that contains a instance of the DbContext?
Does the variable nlaCatalog map to the table in the database?
bmanmike39Author Commented:
When I'm typing it show all the rows in intellisense. regarding the IEnumerable<T>  I cant remember how to view and check that?
it_saigeDeveloperCommented:
In the class definition for nlaCatalog, you would see something like:
public class nlaCatalog : IEnumerable<DataRow>

Open in new window


-saige-
Fernando SotoRetiredCommented:
Hi bmanmike39;

You are working with Entity Framework and NOT DataSet,  DataTable objects correct?
it_saigeDeveloperCommented:
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-
bmanmike39Author Commented:
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.
bmanmike39Author Commented:
and searched for IEnumerable but can't find it in the app_code files
it_saigeDeveloperCommented:
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-
it_saigeDeveloperCommented:
What is the class definition for nlaCatalog?  If you provide that, we can probably provide better assistance rather than stabbing in the dark.

-saige-
Fernando SotoRetiredCommented:
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

bmanmike39Author Commented:
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

it_saigeDeveloperCommented:
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-

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fernando SotoRetiredCommented:
// 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.
bmanmike39Author Commented:
Thanks,  Excellent!
it_saigeDeveloperCommented:
@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-
Fernando SotoRetiredCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.