none
linq to sql RRS feed

  • Question

  • I would like to write a linq to sql statement that will translate to:

    Select field1 from testtable
    where column1='customer' and column2 = 'statename'.

    Can you should me some code that would accomplish this task?

    Thursday, June 7, 2012 5:18 PM

Answers

  • You can return one field like this (if that is what your meant by not "returning the entire class name"):

    var q = from r in yourDataContext.yourTable
            where r.column1 == cust  && r.column2 == state 
            select r.field1; 
    
    //assuming field1 is a string type
    var matchingStrs = q.ToList<string>()
    
    //or for multiple fields, do;
    ...
           select new { r.field1, r.field2 };

    Dan Randolph

    • Marked as answer by midnight_car Sunday, June 10, 2012 4:33 AM
    Thursday, June 7, 2012 10:37 PM
  • #1 MC I believe you are learning C# for #1 has no class, you show a method (which resides in a class). Hence your question about a class makes no sense.

    #2 List<T> is a generic list. The T can hold any type you desire. Hence it can be a List of ints if defined as List<int> myList or a list of bools if defind as List<bool> myList. When he says "yourClass" he means any object you want to put into it. You most likely want to have a list of strings hence it will be List<string> myMatchrecords.

    #3 if the query returns null you need to check for that AND if the list is valid but has no records inserted. That is standard procedure. You do those checks such as this code

    var myResult = q.ToList();
    
    if ((myResult != null) && (myResult.Any())
    {
    
      // do processing of the result
    }
    else
    {
       // Its not valid inform the user and exit and do not process.
    }


    William Wegerson (www.OmegaCoder.Com)

    • Marked as answer by midnight_car Sunday, June 10, 2012 4:31 AM
    Friday, June 8, 2012 2:19 PM
    Moderator

All replies

  • Something like this should do the trick (C#) ...

    var q = from r in yourDataContext.yourTable
            where r.column1 == 'customer' && r.column2 == 'statename'
            select r;
    List<yourClass> myMatchingRecords = q.ToList()


    Brent Spaulding | Access MVP

    Thursday, June 7, 2012 5:44 PM
  • How would you replace the hard-coded values with parameters that are suppiled to the linq statement?
    Thursday, June 7, 2012 7:02 PM
  • string cust = "OmegaMan";
    string state = "CO";
    
    var q = from r in yourDataContext.yourTable
            where r.column1 == cust  && r.column2 == state 
            select r;
    
    List<yourClass> myMatchingRecords = q.ToList()
    See 101 Linq Samples for more help.


    William Wegerson (www.OmegaCoder.Com)


    Thursday, June 7, 2012 7:21 PM
    Moderator
  • You would just use variables in place of your literals ...

    string customer = "";
    string stateName = "";
    customer = "smith";
    stateName = "california";
    var q = from r in yourDataContext.yourTable
            where r.column1 == customer && r.column2 == stateName
            select r;
    List<yourClass> myMatchingRecords = q.ToList()

    Becareful with case, you may want to put .ToUpper() on your string objects.

    Brent Spaulding | Access MVP

    Thursday, June 7, 2012 7:24 PM
  • Sorry OmegaMan ... you posted while I was composing! ... <dazed>

    Brent Spaulding | Access MVP

    Thursday, June 7, 2012 7:31 PM
  • Also the place where I am place the code is in one long if than else statement. I would not think retuurning the entire class name is a good idea since the production code does alot of things.

    how would you suggest I do the following code differently:

    List<yourClass> myMatchingRecords = q.ToList()

    Thursday, June 7, 2012 8:15 PM
  • Also the place where I am place the code is in one long if than else statement. I would not think retuurning the entire class name is a good idea since the production code does alot of things.

    how would you suggest I do the following code differently:

    List<yourClass> myMatchingRecords = q.ToList()

    What? Brent answered your question on how to get data using linq to sql. Whether you have to refactor your code does not come into play with the example.

    William Wegerson (www.OmegaCoder.Com)

    Thursday, June 7, 2012 8:26 PM
    Moderator
  • You can return one field like this (if that is what your meant by not "returning the entire class name"):

    var q = from r in yourDataContext.yourTable
            where r.column1 == cust  && r.column2 == state 
            select r.field1; 
    
    //assuming field1 is a string type
    var matchingStrs = q.ToList<string>()
    
    //or for multiple fields, do;
    ...
           select new { r.field1, r.field2 };

    Dan Randolph

    • Marked as answer by midnight_car Sunday, June 10, 2012 4:33 AM
    Thursday, June 7, 2012 10:37 PM
  • i want to mention the following:

    1. When I am referring to a class I mean something like:

    public void Linq55()
    {
        string[] words = { "cherry", "apple", "blueberry" };
     
        var sortedWords =
            from w in words
            orderby w
            select w;
        var wordList = sortedWords.ToList();
     
        Console.WriteLine("The sorted word list:");
        foreach (var w in wordList)
        {
            Console.WriteLine(w);
        }
    }

    2. in the statement, "List<yourClass> myMatchingRecords = q.ToList()", what is the type for <yourClass>?

    3. If the query does not return any results (gets null), how do you code for that?

    Friday, June 8, 2012 1:12 PM
  • #1 MC I believe you are learning C# for #1 has no class, you show a method (which resides in a class). Hence your question about a class makes no sense.

    #2 List<T> is a generic list. The T can hold any type you desire. Hence it can be a List of ints if defined as List<int> myList or a list of bools if defind as List<bool> myList. When he says "yourClass" he means any object you want to put into it. You most likely want to have a list of strings hence it will be List<string> myMatchrecords.

    #3 if the query returns null you need to check for that AND if the list is valid but has no records inserted. That is standard procedure. You do those checks such as this code

    var myResult = q.ToList();
    
    if ((myResult != null) && (myResult.Any())
    {
    
      // do processing of the result
    }
    else
    {
       // Its not valid inform the user and exit and do not process.
    }


    William Wegerson (www.OmegaCoder.Com)

    • Marked as answer by midnight_car Sunday, June 10, 2012 4:31 AM
    Friday, June 8, 2012 2:19 PM
    Moderator