CODEWITHSUNDEEP
c#asp.netlinq-to-sql
Nathan McKaskle
Nathan McKaskle

Reputation: 3063

How do I add a single row to a list using LINQ to SQL?

So I have a table of office locations and each column contains the relevant information (i.e. Office Name, Address, City, State, Zip, Country).

I have it set up in the interface for users to select their office, the rest is filled in for them.

I know how to use a generic list and populate it with one column with multiple rows, as I did this with the drop down menu that lists office locations from the same table.

What I don't know is how to populate a generic list with multiple columns from a single row.

For example, here is the code I have for the former which works fine:

    private List<string> selectLocs()
    {
        Locs offLoc = new Locs();

        List<string> objList = new List<string>();

        var select = from l in offLoc.Locations
                     orderby l.OFFICE
                     select l;

        foreach (Location loc in select)
        {
            objList.Add(loc.OFFICE);
        }

        return objList;
    }

Here is the code for the latter situation, incomplete, since I have no idea how to do the same thing:

    private List<string> selectAddr(string strLoc)
    {
        List<string> objList = new List<string>();

        Locs offLocs = new Locs();

        var select = from l in offLocs.Locations
                     where l.OFFICE == strLoc
                     select l;

        //foreach what? in select? Do I even use a foreach loop?


        return objList;
    }

Not sure why nobody's asked this before.

Upvotes: 2

Views: 2116

Answers (3)

stevethethread
stevethethread

Reputation: 2524

Sephethus. All of the answers above are valid, but I am not sure they are answering your question. You were asking how to return mutiple columns from a row right?

You can make use of anonymous types to help here, e.g. 

private IList<string> selectAddr(string strLoc)
{
    Locs offLocs = new Locs();
    var xx = (from l in offLocs.Locations
            where l.OFFICE == strLoc
            select new { l.Address1, l.Address2, l.County });

return xx.Select(a=>String.Concat(x.Address1, x.Address2, x.County).ToList();

}

However, you can simplify this even further,

private IList<string> selectAddr(string strLoc)
    {
        Locs offLocs = new Locs();
        return (from l in offLocs.Locations
                where l.OFFICE == strLoc
                select String.Concat(l.Address1, l.Address2, l.County)).ToList();

    }

Or better still

var offLocs = new Locs();
return offLocs.Where(o=>o.Office = strLoc).Select(l=>String.Concat(l.Address1, l.Address2, l.County)).ToList();

But that's just a matter of taste!

Upvotes: 2

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236208

You first method could be simplified:

private List<string> SelectLocations()
{
    Locs offLoc = new Locs();
    return (from l in offLoc.Locations
            orderby l.OFFICE
            select l.OFFICE).ToList();
}

Your second method:

private List<string> SelectAddresses(string strLoc)
{
    Locs offLocs = new Locs();
    return (from l in offLocs.Locations
            where l.OFFICE == strLoc
            select l.ADDRESS).ToList();
}

UPDATE: If you need several attributes, then return full location object:

private List<Location> SelectLocations(string strLoc)
{
    Locs offLocs = new Locs();
    return (from l in offLocs.Locations
            where l.OFFICE == strLoc
            select l).ToList();
}

Also try to avoid using Hungary notation (adding prefixes to variable names, which describe variable types). Instead of strLoc you can use locationName.

Upvotes: 4

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60493

your first method could be

return new Locs().Locations.OrderBy(m => m.Office).Select(l => l.OFFICE).ToList()

Your second query should maybe not return a List, but a location

private Location selectAddr(string strLoc)
{
    return new Locs().Locations.FirstOrDefault(m => m.OFFICE == strlLoc);
}

or if you want a list of ADDRESS(this is not really clear)

private IList<string> selectAddr(string strLoc)
{
    return new Locs().Locations.Where(m => m.OFFICE == strlLoc)
    .Select(l => l.ADDRESS).ToList();
}

or a list of Locations

private IList<Location> selectAddr(string strLoc)
{
    return new Locs().Locations.Where(m => m.OFFICE == strlLoc)
    .ToList();
}

Upvotes: 3

Related Questions