locked
I need help creating a query in LINQ to Entities RRS feed

  • Question

  • Hi

    I'm using SQL Server 2014 Express with C# in Visual Studio 2013 Express.

    I'm trying to grab the Primary Key value from a table by first assigning the result of a select query to a variable (called thisVar) using LINQ to Entities. But I don't know how to convert what I have written down in SQL to work as LINQ to Entities.

    I'm trying to use a string variable (called thatVar) to filter on the field1 column and I want to assign the tableID value to thisVar, which I would then convert to an int (in the variable thisVarID).  The table is:

    name of table: table1

    column names are:

    Primary Key table1ID int(4)

    field1 nchar(10)

    I imagine the code with normal SQL to be something like this:

    using (testEntities data = new testEntities());

    var thisVar = (Select tableID

    FROM table1

    Where field1 = thatVar;

    int thisVarID = Convert.ToInt32(thisVar));

    Is there a good online tutorial or a free tool that will help me understand how to make this work in LINQ to Entities?

    Thanks



    • Edited by thisman Sunday, June 15, 2014 2:06 PM
    Sunday, June 15, 2014 2:05 PM

Answers

All replies

  • var id = (from a in table1 where a.tableID == thisVar select a.tableID);

    http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

    http://www.linqpad.net/

    • Marked as answer by thisman Monday, June 16, 2014 7:13 AM
    • Unmarked as answer by thisman Monday, June 16, 2014 7:13 AM
    • Marked as answer by thisman Monday, June 16, 2014 10:51 AM
    Sunday, June 15, 2014 5:27 PM
  • Hi,

    You could use either of these to statements;

    int thisVar = (from a in table1 where a.field1 == thatVar select a.table1ID).Single();
     
    int thisVar2 = table1.Where(a => a.field1 == thatVar).Single().table1ID;

    Note that if there is not exactly one matching row, these statements will through an exception. You can use "SingleOrDefault()" if there could be 0 or 1 matches. If there could be more than one match, you'll need to decide how to choose the value to take. For example, you can sort them, and then use Take(1) to get the first row.

    Hope that helps,

    Nick.

    Monday, June 16, 2014 9:36 AM
  • Thanks for the answers darnold924 and Nick.  I since figured I was looking for a parameter query and I managed to come up with one after looking at tutorials online. But your answers look really good. I wish I could mark both answers as "the answer".

    • Edited by thisman Monday, June 16, 2014 10:56 AM
    Monday, June 16, 2014 10:55 AM