Solved

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

Posted on 2014-11-03
21
247 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
[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
  • 9
  • 7
  • 5
21 Comments
 
LVL 34

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 34

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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 34

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 63

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 34

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 63

Expert Comment

by:Fernando Soto
ID: 40420768
Hi bmanmike39;

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

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
 

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 34

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 34

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 63

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 34

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 63

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 34

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 63

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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 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