Solved

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

Posted on 2014-11-03
21
237 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 32

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 32

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 32

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 32

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 32

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 32

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 32

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 32

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 32

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 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

10 Experts available now in Live!

Get 1:1 Help Now