switch statement in linq

Question

My code for sql connection using linq is:

var query1 = from u in dc.Usage_Computers
          where u.DomainUser == s3
          select u; // selects all feilds from table

GridView1.DataSource = query1;
GridView1.DataBind();

I have a field called "Operation" in the table "Domainuser" which has values like "1, 2, 3". When I populate these values to data grid I wanted to convert them to meaningful values like if the value of Operation is 1 then display in datagrid as "logon", if 2 then "logoff" etc...

How do i assign values for them after retrieving from database?

possible duplicate of [linq case statement](http://stackoverflow.com/questions/936028/linq-case-statement) — Ryan Gates, Oct 10 '14 at 18:20

Amy B · Accepted Answer · 2008-10-16T20:16:51.630

15

This technique does not seem particularly applicable to your problem, but here it is anyway.

You can create a SQL case statement in LinqToSql by using the C# ? : operator.

var query1 =
  from u in dc.Usage_Computers
  where u.DomainUser == s3
  select new {usage = u, 
    operation =
      u.DomainUser.Operation == 1 ? "login" :
      u.DomainUser.Operation == 2 ? "logoff" :
      "something else"
  };

edited Oct 16 '08 at 20:16

answered Oct 16 '08 at 20:00

Amy B

108,202
21
135
185

1

Why people want the database to transform ints into label strings for them, I'll never know. Seems like a job for the webserver. – Amy B Oct 16 '08 at 20:02
while the solution answers the question, I think it is a bad practice to sprinkle conversion logic like that. Such conversion logic should be centralized into a reusable class. – Samuel Kim Oct 17 '08 at 02:57
David, when given the opportunity to perform a simple conversion like this in either the web server or the database, you can consider which engine has the least load already, and do the conversion there. Often, there's more CPU available in the database server. – DOK Oct 20 '08 at 15:37
3

It's not about CPU availability. All this logic has to be transmitted into the database, with a bunch of parameters. Then the database has to send back strings when it could have sent back ints (smaller network footprint of request and response). Doing this is like attaching blades to the spokes of a bicycle and riding around in the grass to mow your lawn. Wrong tool for the job, even if it works. – Amy B Nov 03 '10 at 14:17

score 2 · Answer 2 · answered Oct 16 '08 at 19:50

Use a template field in your gridview:

<asp:GridView ID="gvDomain" runat="server" OnRowDataBound="gvDomain_RowDataBound">
    <Columns>
        <asp:TemplateField>
             <HeaderTemplate>
                 Operation
             </HeaderTemplate>
             <ItemTemplate>
                 <asp:Label id="lblLogon" runat="server" />
             </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Then use the gridviews RowDataBound event to discover the label and assign its text:

Protected Sub gvDomain_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvStates.RowDataBound
    Dim lblLogon As Label = DirectCast(e.Row.FindControl("lblLogon"), Label)
    Dim drv As DataRowView = DirectCast(e.Row.DataItem, DataRowView)

    If lblLogon IsNot Nothing Then
        Select Case drv("Operation").ToString()
            Case "1" 
                lblLogon.Text = "Logon"
                Break
            Case "2"
                lblLogon.Text = "Logoff"
                Break
            //etc...
        End Select
    End If
End Sub

David Alpert · Answer 3 · 2008-10-16T20:05:38.140

static Func<int?, string> MapSqlIntToArbitraryLabel = (i =>
{
   // for performance, abstract this reference 
   //  dictionary out to a static property
   Dictionary<int, string> labels = new Dictionary<int, string>();
   labels.Add(1, "logon");
   labels.Add(2, "logoff");
   labels.Add(...);

   if (i == null) throw new ArgumentNullException();
   if (i < 1 || i > labels.Count) throw new ArgumentOutOfRangeException();

   return labels.Where(x => x.Key == i.Value)
                .Select(x.Value)
                .Single();
}

that return statement can also be expressed as:

return (from kvp in labels
        where kvp.Key == i.Value
        select kvp.Value).Single();

Then you can use call that function from your linq query like so:

var query1 = from u in dc.Usage_Computers 
             where u.DomainUser == s3 
             select {
                 Operation = MapSqlIntToArbitraryLabel(u.Operation)
                 // add other properties to this anonymous type as needed
             };

I've tried every suggested method of fooling Linq2Sql into running my code and this method is the only one that i've found that allows me to run code as part of a deferred-execution projection.

while i agree in general with the approach (+1), think it's a little overkill. A simple dictionary may suffice (and provide better performance). Also should initialise once only. Only other thing is consideration for making the translation method an extension method to the object. — Samuel Kim, Oct 17 '08 at 03:02
Extension methods on the object were my first try, but i have found this approach generalizes well - i.e., from within MapSqlIntToArbitraryLabel() you could call a method that is not recognized by Linq2Sql and it appears to run without choking on the "no translation to sql" issue. — David Alpert, Oct 17 '08 at 16:55

score 0 · Answer 4 · answered Oct 16 '08 at 19:33

I've done something similar using TemplateFields. Using an ASP:Label bound to the property and adding an OnPreRender event handler for the control. In the event handler for the control I translate the text based on it's current value and set the new value:

protected void label_OnPreRender( object sender, EventArgs e )
{
   Label l = (Label)sender;
   switch (l.Text) {
      case "1":
         l.Text = "Logon";
         break;
       ...
      default:
         break;
   }
}

If the form is in edit mode, you'll need to handle it differently. You'll also probably need to add handlers for Inserting and Updating to the View control you are using to translate the data supplied by the page into its database representation.

switch statement in linq

4 Answers4

Linked